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

Anonymous — Tue, 03 Oct 2023 18:15:17 GMT

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.

Market	Loads	ratio	weighted loads
CA_FRSV	100	6.9	690
CA_FRSR	50	3.6	180
	150	5.8	870

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.

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

Anonymous — Tue, 03 Oct 2023 19:14:02 GMT

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

topic Re: DAX help with nested If statements when measure value is null or not in DAX Commands and Tips

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

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