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

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.

Reply
Philfound1199
Frequent Visitor

DAX help with nested If statements when measure value is null or not

In power BI I've created a measure that is calculating the average ratio per market weighted by the number of loads. So in excel it looks like below and I've translated that into some measures in power BI.

MarketLoadsratioweighted loads
CA_FRSV1006.9690
CA_FRSR503.6180
 1505.8870

 

Each market can be split into two categories V and R, some markets may have loads from both categories and some may only have loads from one or the other. The issue I've running into is that the measure I've created appears to be working just fine when a market has loads from both categories but when a market only has loads from one category it doesn't appear to be functioning properly. I've tried using some nested if statements but I can quite get it to work.

I'm sure theres a way to condense things but what I ended up doing is creating two measures to calculate the weighted loads for each category that looks like this (each category for the same market can have a different ratio):

ratioR = CALCULATE([Sum Loads]*MAX(DatShipper[RATIO]),DatShipper[EQUIP_TYPE] = "R")
ratioR = CALCULATE([Sum Loads]*MAX(DatShipper[RATIO]),DatShipper[EQUIP_TYPE] = "V")

And then another measure to calculate the weighted average:

ShipAvgRatio = DIVIDE(CALCULATE([ratioV]+[ratioR]),[Sum Loads])

That measure is working fine for markets that have a number of loads in both categories but for markets that only have loads from one or the other it's not working proerply.

What would be the best way to create a measure that if a market only has loads for one equip type (V or R) then just use the DatShipper[RATIO] otherwise use DIVIDE(CALCULATE([ratioV]+[ratioR]),[Sum Loads]).

Other than the number of loads which I'm getting from another related table all other values live in the same table.
1 ACCEPTED SOLUTION
Philfound1199
Frequent Visitor

I think I figured it out using the following:

ShipAvgRatio = IF(ISBLANK(MAX(DatShipper[EQUIP_TYPE])),MAX(DatShipper[RATIO]), DIVIDE(CALCULATE([ratioV]+[ratioR]),[Sum Loads]))

Appears to be working properly

View solution in original post

1 REPLY 1
Philfound1199
Frequent Visitor

I think I figured it out using the following:

ShipAvgRatio = IF(ISBLANK(MAX(DatShipper[EQUIP_TYPE])),MAX(DatShipper[RATIO]), DIVIDE(CALCULATE([ratioV]+[ratioR]),[Sum Loads]))

Appears to be working properly

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.

Top Solution Authors