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.
