Better Average per Category 09-09-2022 10:41 AM - last edited 10-09-2022 05:11 AM

[Greg_Deckler]

Microsoft's "Average per category" quick measure does not suffer the same fatal flaw as "Running total", you know, the one where it doesn't work when used in single table models. However, it is stupidly complicated. It's like someone sat down and said, "You know, I could solve this in 5 minutes but the solution wouldn't include CALCULATE". And then they spent the next 3 months figuring out how to incorporate CALCULATE into the calculation. You can find out more about this method at the end of the video here: Video details - YouTube Studio

It's absurd, no sane person would construct the calculation this way:

Value average per Month = 
AVERAGEX(
	KEEPFILTERS(VALUES('Table'[Month])),
	CALCULATE(SUM('Table'[Value]))
)

Even seasoned DAX pros would probably initially scratch their heads a little having a CALCULATE statement with no filter clause whatsoever. Disreading the fact that this formula uses VALUES and anything VALUES can do DISTINCT can do better, what is going on here is complex and not easily explained to someone new to DAX. For one thing, it's very odd to see a KEEPFILTERS statement as not being part of a CALCULATE statement since the documentation on KEEPFILTERS specifically notes that it is designed for use in CALCULATE statements. However, KEEPFILTERS does return a table, which is why it is valid as the first parameter for AVERAGEX even though no sane individual would probably think of doing it that way. And, by using an X aggregator (AVERAGEX) the second parameter (the expression) is evalulated in the context of the table expression. So, it works.

By why? All you want to do is SUM up some column grouped by a category and then take the average of those values. Why all the jumping through hoops and twisting your context into knots when you can accomplish the exact same thing much more intuitively, as in:

Better Average per Category = 
    VAR __Table = SUMMARIZE('Table',[Month],"Value",SUM('Table'[Value]))
RETURN
    AVERAGEX(__Table, [Value])

Super simple and straight-forward. SUMMARIZE the table by your category (month) and SUM the values. Take the average across that table. Simple, straight-forward and far less complicated to explain. Oh, you don't like SUMMARIZE because of that one blog article that pointed out that in incredibly rare circumstances that you are almost certainly never to encounter that you might get wonky results? Fine, use GROUPBY.

Better Average per Category 2 = 
    VAR __Table = GROUPBY('Table',[Month],"Value",SUMX(CURRENTGROUP(),'Table'[Value]))
RETURN
    AVERAGEX(__Table, [Value])

 I say again. Create a table VAR. Use an X aggregator. It works and you don't need CALCULATE.

Watch the video!

eyJrIjoiMDQzMjdkNTQtOTJkZC00NDI2LTgxZjktZWQxMjNmYjUwMzVlIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9