SOLVED

Iron Contributor

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

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

# Re: DAX: how to use measures instead of calculated columns?

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

# Re: DAX: how to use measures instead of calculated columns?

Wow, fabulous! Thank you!

# Re: DAX: how to use measures instead of calculated columns?

@bartvana , you are welcome

1 best response

Accepted Solutions
best response confirmed by bartvana (Iron Contributor)
Solution

# Re: DAX: how to use measures instead of calculated columns?

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