Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculating IF at row level

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%" )
)))))))))))

2 REPLIES 2

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.

Anonymous
Not applicable

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors