DAX: how to use measures instead of calculated columns?

Hello,

In DAX I need to calculate some statistics on the following kind of data (see also attached workbook). I can get it done using calculated columns and ALLEXCEPT to filter by Product, but I have the feeling that's not the most elegant or efficient way.

Eg. for the weighted mean by Product I do this:

=CALCULATE(SUM(tFacts[Month * AvgQty])/SUM(tFacts[AvgQty]); ALLEXCEPT(tFacts; tFacts[Product]))

And then in another column I use this weighted mean like this:

=(tFacts[AvgQty]-tFacts[MeanByProduct])^2

So question 1 is: can this be done using measures instead of calculated columns, and if so, how?

Also, to make it work on aggregations (eg. PROD01 and PROD02 together), first the AvgQty should be averaged by month. So the data for PROD01 and PROD02 taken together should be:
Month ; AvgQTy
1 ; AVERAGE(AvgQty PROD01 month 1; AvgQty PROD02 month 1)

2 ; AVERAGE(AvgQty PROD01 month 2; AvgQty PROD02 month 2)

etc., and on the basis of that the statistics should be calculated.

I'm at a loss on how to get that done? So Question 2 is how to do that correctly?

Many thanks in advance for any guidance you can provide...

question-dax-statistics-by-group.xlsx191 KB

You calculate average of average here. The pattern for such calculation is using of AVERAGEX / SUMMARIZE.

Measures could be as

Avr Of Mean Measure:=VAR sumCat=
    SUMMARIZE (
        tFacts,
        tFacts[Category],
        tFacts[Product],
        "sumQtyOnMonth", SUMX ( tFacts, tFacts[AvgQty] * tFacts[Month] ),
        "sumQty",    SUM ( tFacts[AvgQty] )
    )
VAR Result=
    AVERAGEX ( 
       sumCat,
       DIVIDE ( [sumQtyOnMonth], [sumQty] )
    )
RETURN Result

and

Avr Of StDev Measure:=AVERAGEX (
    SUMMARIZE (
        tFacts,
        tFacts[Category],
        tFacts[Product],
        "variance",
            SUMX (
                tFacts,
                (
                     tFacts[AvgQty]
                        - CALCULATE ( [Avr Of Mean Measure], ALLEXCEPT ( tFacts, tFacts[Product] ) )
                ) ^ 2
           )
               / SUM ( tFacts[AvgQty] )
     ),
     SQRT ( [variance] )
)

Compare to current measures result is

question-dax-statistics-by-group.xlsx198 KB

Avr Of Mean Measure:=VAR sumCat= SUMMARIZE ( tFacts, tFacts[Category], tFacts[Product], "sumQtyOnMonth", SUMX ( tFacts, tFacts[AvgQty] * tFacts[Month] ), "sumQty", SUM ( tFacts[AvgQty] ) ) VAR Result= AVERAGEX ( sumCat, DIVIDE ( [sumQtyOnMonth], [sumQty] ) ) RETURN Result

Avr Of StDev Measure:=AVERAGEX ( SUMMARIZE ( tFacts, tFacts[Category], tFacts[Product], "variance", SUMX ( tFacts, ( tFacts[AvgQty] - CALCULATE ( [Avr Of Mean Measure], ALLEXCEPT ( tFacts, tFacts[Product] ) ) ) ^ 2 ) / SUM ( tFacts[AvgQty] ) ), SQRT ( [variance] ) )

Forum Discussion

DAX: how to use measures instead of calculated columns?

Share

Resources