topic Re: DAX measure for sum of average by grouping in DAX Commands and Tips

DAX measure for sum of average by grouping

Anonymous — Fri, 17 Apr 2020 22:10:03 GMT

Hi everyone, I'm looking for help creating this DAX measure after spending some time trying my own solutions and search online for something similar, to no avail. Here what my starting table looks like:

Group	Year	Year-Quarter	Value
A1	2019	2019Q3	30
A1	2019	2019Q4	30
A1	2020	2020Q1	40
A2	2019	2019Q3	3
A2	2019	2019Q4	3
A2	2020	2020Q1	4
B1	2019	2019Q3	20
B1	2019	2019Q4	20
B1	2020	2020Q1	30

For a given Group, for a given Year, the Value field will be duplicated for each quarter. I need to create a measure that gives my the sum of the Value column by each unique Year-Group combo. The simplest example is if I have a slicer with A1 selected and a date slider filtering on 2019Q3-2019Q4, the DAX measure should output 30. A slightly more complicated example is if I have a slicer filtering for A1 and A2, and a date slider with 2019Q3-2019Q4 selected, the measure should output 33, since that is the sum of the unique Year-Group values. I've tried all manner of CALCULATE, SUMX, and GROUPBY statements and really am not getting anywhere. Any ideas? I'm sure there is a simple answer, thanks!

Re: DAX measure for sum of average by grouping

Greg_Deckler — Sat, 18 Apr 2020 03:58:56 GMT

I would think:

Measure = VAR __Table = SUMMARIZE( 'Table', [Group], [Year], "Value",AVERAGE([Value]) ) RETURN SUMX(__Table,[Value])

Re: DAX measure for sum of average by grouping

Anonymous — Sat, 18 Apr 2020 16:36:20 GMT

SUMX( SUMMARIZE( FT, FT[Group], FT[Year] ), CALCULATE( SUMX( VALUES( FactTable[Value] ), FactTable[Value] ) ) )

Even though the above works, I'm afraid it could sometimes return incorrect results. Anything (the above formula from Greg included) that you calculate on a single fact table CAN return an incorrect result as the below article explains:

https://www.sqlbi.com/articles/understanding-dax-auto-exist/

Therefore, you should create a star-schema as the model for your data. Don't do it and you'll be creating wrong figures you will not even be aware of.

If your model is correct, then this will do what you want:

SUMX( SUMMARIZE( FT, Groups[Group], Calendar[Year] ), CALCULATE( SUMX( VALUES( FactTable[Value] ), FactTable[Value] ) ) )

Best

Re: DAX measure for sum of average by grouping

Anonymous — Mon, 20 Apr 2020 17:51:19 GMT

Excellent, this works perfectly. Regarding the model, this table is being sliced by a series of master tables in a star format so I think there should be no issues there. I'm still learning about how to build good data relationships and DAX as well so all of this is helpful! DAX measures in particular are difficult to wrap my head around...