Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
@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.
@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))
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
44 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
70 | |
49 | |
45 | |
20 | |
16 |