SOLVED

DAX: how to use measures instead of calculated columns?

%3CLINGO-SUB%20id%3D%22lingo-sub-2746633%22%20slang%3D%22en-US%22%3EDAX%3A%20how%20to%20use%20measures%20instead%20of%20calculated%20columns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2746633%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EIn%20DAX%20I%20need%20to%20calculate%20some%20statistics%20on%20the%20following%20kind%20of%20data%20(see%20also%20attached%20workbook).%20I%20can%20get%20it%20done%20using%20calculated%20columns%20and%20ALLEXCEPT%20to%20filter%20by%20Product%2C%20but%20I%20have%20the%20feeling%20that's%20not%20the%20most%20elegant%20or%20efficient%20way.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22bartvana_1-1631554236416.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F310069i3395CDB5DDA8BCD2%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22bartvana_1-1631554236416.png%22%20alt%3D%22bartvana_1-1631554236416.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EEg.%20for%20the%20weighted%20mean%20by%20Product%20I%20do%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-c%22%3E%3CCODE%3E%3DCALCULATE(SUM(tFacts%5BMonth%20*%20AvgQty%5D)%2FSUM(tFacts%5BAvgQty%5D)%3B%20ALLEXCEPT(tFacts%3B%20tFacts%5BProduct%5D))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20then%20in%20another%20column%20I%20use%20this%20weighted%20mean%20like%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-c%22%3E%3CCODE%3E%3D(tFacts%5BAvgQty%5D-tFacts%5BMeanByProduct%5D)%5E2%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ESo%20question%201%20is%3A%20can%20this%20be%20done%20using%20measures%20instead%20of%20calculated%20columns%2C%20and%20if%20so%2C%20how%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20to%20make%20it%20work%20on%20aggregations%20(eg.%20PROD01%20and%20PROD02%20together)%2C%20first%20the%20AvgQty%20should%20be%20averaged%20by%20month.%20So%20the%20data%20for%20PROD01%20and%20PROD02%20taken%20together%20should%20be%3A%3CBR%20%2F%3EMonth%20%3B%20AvgQTy%3CBR%20%2F%3E1%20%3B%20AVERAGE(AvgQty%20PROD01%20month%201%3B%20AvgQty%20PROD02%20month%201)%3C%2FP%3E%3CP%3E2%20%3B%20AVERAGE(AvgQty%20PROD01%20month%202%3B%20AvgQty%20PROD02%20month%202)%3C%2FP%3E%3CP%3Eetc.%2C%20and%20on%20the%20basis%20of%20that%20the%20statistics%20should%20be%20calculated.%3C%2FP%3E%3CP%3EI'm%20at%20a%20loss%20on%20how%20to%20get%20that%20done%3F%20So%20Question%202%20is%20how%20to%20do%20that%20correctly%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20in%20advance%20for%20any%20guidance%20you%20can%20provide...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2746633%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2749152%22%20slang%3D%22en-US%22%3ERe%3A%20DAX%3A%20how%20to%20use%20measures%20instead%20of%20calculated%20columns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2749152%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F863782%22%20target%3D%22_blank%22%3E%40bartvana%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20calculate%20average%20of%20average%20here.%20The%20pattern%20for%20such%20calculation%20is%20using%20of%20AVERAGEX%20%2F%20SUMMARIZE.%3C%2FP%3E%0A%3CP%3EMeasures%20could%20be%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3EAvr%20Of%20Mean%20Measure%3A%3DVAR%20sumCat%3D%0A%20%20%20%20SUMMARIZE%20(%0A%20%20%20%20%20%20%20%20tFacts%2C%0A%20%20%20%20%20%20%20%20tFacts%5BCategory%5D%2C%0A%20%20%20%20%20%20%20%20tFacts%5BProduct%5D%2C%0A%20%20%20%20%20%20%20%20%22sumQtyOnMonth%22%2C%20SUMX%20(%20tFacts%2C%20tFacts%5BAvgQty%5D%20*%20tFacts%5BMonth%5D%20)%2C%0A%20%20%20%20%20%20%20%20%22sumQty%22%2C%20%20%20%20SUM%20(%20tFacts%5BAvgQty%5D%20)%0A%20%20%20%20)%0AVAR%20Result%3D%0A%20%20%20%20AVERAGEX%20(%20%0A%20%20%20%20%20%20%20sumCat%2C%0A%20%20%20%20%20%20%20DIVIDE%20(%20%5BsumQtyOnMonth%5D%2C%20%5BsumQty%5D%20)%0A%20%20%20%20)%0ARETURN%20Result%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EAvr%20Of%20StDev%20Measure%3A%3DAVERAGEX%20(%0A%20%20%20%20SUMMARIZE%20(%0A%20%20%20%20%20%20%20%20tFacts%2C%0A%20%20%20%20%20%20%20%20tFacts%5BCategory%5D%2C%0A%20%20%20%20%20%20%20%20tFacts%5BProduct%5D%2C%0A%20%20%20%20%20%20%20%20%22variance%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20SUMX%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20tFacts%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20tFacts%5BAvgQty%5D%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20-%20CALCULATE%20(%20%5BAvr%20Of%20Mean%20Measure%5D%2C%20ALLEXCEPT%20(%20tFacts%2C%20tFacts%5BProduct%5D%20)%20)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%20%5E%202%0A%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2F%20SUM%20(%20tFacts%5BAvgQty%5D%20)%0A%20%20%20%20%20)%2C%0A%20%20%20%20%20SQRT%20(%20%5Bvariance%5D%20)%0A)%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ECompare%20to%20current%20measures%20result%20is%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20574px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F310308i869D3B87A8D3EEDB%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Contributor

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.

bartvana_1-1631554236416.png

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...

3 Replies
best response confirmed by bartvana (Frequent Contributor)
Solution

@bartvana 

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

image.png

Wow, fabulous! Thank you!