topic Re: Average from calculated measure in DAX Commands and Tips

Average from calculated measure

Anonymous — Thu, 27 Aug 2020 01:19:26 GMT

I have a calculated measure to return the date diff from from different rows based on job, status, and date. But I also need the measure to average when in Values field in the matrix visual.

Table 1:

Table 2:

Calucated Measure:

Days Confirmed to Shipped =

VAR M =

CALCULATE(

MAX('(a) Table 1’[changed_date]),

FILTER(ALL('Table 1’),'Table 1’ [Job Id] = MAX(Table 1’[Job ID])),

FILTER(

'Table 2’,

'Table 2’[name] = "Shipped"

)

VAR N =

CALCULATE(

MIN('Table 1’[changed_date]),

FILTER(ALL('Table 1),'Table 1’[Job Id] = MAX(‘Table 1’[Job Id])),

FILTER(

'Table 2,

'Table 2’[name] = "Confirmed"

)

Return

IF(m=MAX('(a) national_work_instance_status_change'[changed_date]),DATEDIFF(N,M,DAY),0)

Thank you in advance for your help.

Re: Average from calculated measure

Greg_Deckler — Thu, 27 Aug 2020 02:00:17 GMT

Anonymous - Not entirely clear, but maybe this is 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.

Re: Average from calculated measure

amitchandak — Thu, 27 Aug 2020 02:58:41 GMT

Anonymous , try a formula like

AverageX(summarize(Table, Table[Job Id], "_1", calculate(Max(Table[status_date]),table[status id] =2), "_2", calculate(Max(Table[status_date]),table[status id] =1)), datediff([_2],[_1],day))