Forum Discussion
Average Per Year vs. Per Quarter
I am trying to determine the Average Per Year vs. Average Per Quarter. The problem that I am having is that both values are coming out to the same value, which is obviously incorrect because the total number of hours each quarter is 2190. Pretty sure the problem is that I am not considering the blank values and my DAX formula is incorrect. How would you approach this?
Current Value Output -> Avg Per Year = 1,783 / Avg Per Qtr = 1,783
Current DAX formula (Avg Per Year) = AVERAGEX( VALUES( Calendar[Date].[Year] ), CALCULATE( SUM( [TotalValues] )))
Current DAX formula (Avg Per Qtr) = AVERAGEX( VALUES( Calendar[Date].[QuarterNo] ), CALCULATE( SUM( [TotalValues] )))
| 2016 | 2017 | 2018 | 2019 | TOTAL | |
| Qtr 2 | 140.63 | 2858.01 | 2938.06 | 493.39 | 6430.09 |
| Qtr 1 | 799.58 | 2812.2 | 2281.75 | 5893.53 | |
| Qtr 4 | 66.26 | 1972.98 | 2534.95 | 4574.19 | |
| Qtr 3 | 456.1 | 1708.25 | 2333.74 | 4498.09 | |
| TOTAL | 662.99 | 7338.82 | 10618.95 | 2775.14 | 21395.9 |
3 Replies
- Michiel
Resolver III
Well, obviously both answers are incorrect: for average per year I'd expect 21395/4, for average per quarter I'd expect either 21395/16 or 21395/13, depending on whether or not you want to count empty quarters. Neither of these result in 1783.
I see one error in your calculation for quarter, as VALUES(Calendar[Date].[QuarterNo]) will be not more than 4 rows.
The approach would be to break down your calculation: does SUM([TotalValues]) return 21395? What does COUNTROWS(VALUES(Calendar[Date].[Year]))? Etc. My suspicion is that there something going on in your [TotalValues] (measure? column?)
Michiel thank you for your response. I did not mention that the resulting output was for 3 customers.
EX: Avg Per Year for 3 Customers = 21,395.89 / 4yrs = 5,348.97 -> 5,348.97 / 3customers = 1,782.99 (1,783)
- Michiel
OK, then the problem is indeed in the count of quarters. You need to divide by the number of quarters/years, not quarter numbers. The easiest way to do this is to have a column 'Year-Quarter' in your Calendar table, and use that.
