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 data which when I can get into this form using a summarize;
VAR Product = SELECTEDVALUE(Products[GenericDescription]) VAR Module = SELECTEDVALUE(ModuleFaults[new_moduletype]) VAR FailType = SELECTEDVALUE(ModuleFaults[new_fault]) VAR FailuresGrouped = SUMMARIZE( ALL('ModuleFaults'), Incident[CompletedMonthYear], Incident[ITMGEDSC], ModuleFaults[new_moduletype], ModuleFaults[new_fault], "MonthlyFailures", COUNTROWS(ModuleFaults) )
I'm trying to create a measure which will show a sum of the MonthlyFailures column (per Widget, Module, Fault) for each MonthYear row for the previous 3 months. So the output should look like so...
I've tried doing a SUMX but I just keep getting the total number of rows back.
VAR FailCountByType = SUMX( FILTER(FailuresGrouped, Incident[CompletedMonthYear] >= FIRSTDATE(DATEADD('Incident'[CompletedMonthYear], -3, MONTH)) && Incident[CompletedMonthYear] <= EOMONTH(Incident[CompletedMonthYear], 0) && ModuleFaults[new_moduletype] = Module && ModuleFaults[new_fault] = FailType && Incident[ITMGEDSC] = Product ), [MonthlyFailures] )
Hi @sparkymark75 ,
One sample for your reference. Please check the following steps as below.
1. To create a calculated column in your summrized table.
rank = RANKX ( ALL ( 'Table1' ), Table1[Month Year],, ASC, DENSE )
2. To create a measure to get the excepted result we need.
Measure 2 = VAR ran = MAX ( Table1[rank] ) - 2 RETURN CALCULATE ( SUM ( Table1[MonthFailures] ), FILTER ( ALLSELECTED ( Table1 ), Table1[rank] >= ran && Table1[rank] <= MAX ( Table1[rank] ) ) )
Pbix as attached.
Thanks for replying. I'm guessing I didn't word my question as clearly as I should have. The summary table is calculated inline in the measure as a variable, e.g.
VAR FailuresGrouped = SUMMARIZE( FILTER(ALL('ModuleFaults'), ModuleFaults[CaseCompletedOn] <> BLANK()), Incident[CompletedMonthYear], Incident[ITMGEDSC], ModuleFaults[new_moduletype], ModuleFaults[new_fault], "MonthlyFailures", COUNTROWS(ModuleFaults) )
Is it possible to use the RANKX on a table in a variable and then the subsequent measure code you have?
Hi @sparkymark75 ,
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |