Calculate average of a measure

[CairoXimenes]

Hi there,

I need to calculate the average efficiency (LE) for some production lines, which consists in dividing total quantity produced by theoretical quantity. It may be individually computed as measures, as shown below.

TotalQTY = CALCULATE(SUM(APPOINTMENTS[QTY]))

TheoreticalQTY = CALCULATE(SUMX(CYCLE, [PlannedTimeHM]*[TheoreticalCycleHM]))

LEHMTF = CALCULATE((DIVIDE(TotalQTY, TheoreticalQTY)))

However, I need to aggregate the total efficiency as the average of the individual measures. What is the best way to do this?

[Anonymous · Accepted solution]

Hi @CairoXimenes ,

Please try below steps:

1. below is my test table
Table:

2. create a measure with below dax formula

Avg LE =
VAR cur_mq =
    SELECTEDVALUE ( 'Table'[Maquina] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[Maquina] = cur_mq )
RETURN
    AVERAGEX ( tmp, [LE] )

Measure =
VAR _val =
    SUMX ( ALL ( 'Table' ), [LE] )
VAR ctn =
    COUNTROWS ( 'Table' )
RETURN
    IF ( HASONEVALUE ( 'Table'[Maquina] ), [Avg LE], DIVIDE ( _val, ctn ) )

3. add table visual with fields and measure, I provide two table visual  for you, please choose what you need

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mangaus1111]

Hi @CairoXimenes ,

is this your expected result?

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mangaus1111]

Otherwise you can use ALL.

Measure = CALCULATE([LE],

                 ALL('Facts50'[Qty Produzida]),ALL('Facts50'[Qty teorica])

)

[CairoXimenes]

I believe it shows the right path, but I am not sure if ALLEXCEP function is applicable to my case. Is there other way?

[Greg_Deckler]

@CairoXimenes First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.