Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
Can anyone suggest where I am going wrong please? I need to create a measure that gives a classification to a row, based on the result of another measure. I have to create this as a measure as I don't have access to make a calculated column.
I have made measure 1 and measure2 to use withnin measure 3.
When I add measure 1 and 2 to my data the rows remain in the table as expected, the measures are applied for each row.
When I filter to show only one row, and add my thrid measure this also works ok.
When I show more rows, the measure 3 doens't appear to work at indivdual row level.
Can anyone suggest what I need to do for this to work correctly please?
Measure 1:
TimeDiff_ActualVsPlanned = SUMX('TABLE1',
'TABLE1'[Duration Minutes]-[Planned Minutes])
Measure 2:
PlannedVsActualVariant% = divide([TimeDiff_ActualVsPlanned],[Planned Minutes])
Measure 3:
OverUnderVarBracket =
IF (
[PlannedVsActualVariant%] < -0,
"less by under 25%",
IF (
[PlannedVsActualVariant%] < -0.251,
"underrun by 25-49%",
IF (
[PlannedVsActualVariant%] < 0.501,
"underrun by 50-84%",
IF (
[PlannedVsActualVariant%] < -0.851,
"underrun by 85-100%",
IF (
[PlannedVsActualVariant%] < -1,
"underrun by 100-149%",
IF (
[PlannedVsActualVariant%] < -1.5,
"underrun by 150% or more",
IF (
[PlannedVsActualVariant%] > 1.5,
"over by 150% or more",
IF (
[PlannedVsActualVariant%] >= 1,
"overun 100-149%",
IF (
[PlannedVsActualVariant%] > 0.851,
"overrun 85-100%",
IF (
[PlannedVsActualVariant%] > 0.501,
"overrun 50-84%",
IF (
[PlannedVsActualVariant%] > 0.251,
"overrun 25-49%",
IF ( [PlannedVsActualVariant%] > 0, "overrun less than 25%" )
)))))))))))
Hey, can you share some sample data in a xlsx/pbix file? Also I would rather work with SWITCH instead of multiple if statements for readability purposes.
Hi, applogies, I don't seem to be able to attach anything. I am wondering if because [Duration Minutes] and [Planned Minutes] are measures, this is why it is not working at row level, because they are already aggregated?
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |