This is a fun tutorial where we will learn how to calculate average of the highest values that might be spread across different time periods. A good example is figuring out what would be a good proxy for the highest temperature for IL in July. If we simply take one reading for the highest day by zip code, we are running a risk of using an outlier due to a bad or defective reading of temperature. However, if we find the highest reading in IL for all zip codes (which might be measured on different days) then we can average those values to come up with a more representative measure of the highest temperature for IL.
There are many business scenarios like the one above, so in this tutorial we will learn how we can write a fairly simple calc to get us what we want using AVERAGEX() and ADDCOLUMNS() functions. In this video I will explain how these functions work and how they help us implement a calculation for the average of highest scores by a subject. As a bonus, I also implement a calculation to do the average of highest scores from the very last test taken by our students.
You can download the tutorial materials here.
business intelligist 