need a measure to divide, but with different denominators depending on the dimension value

[scotjn1]

I have vehicle cost and mileage data in my data model.  The costs are broken out by category, and I'm trying to create a measure to calculate cost-per-mile based on total miles driven, similar to the image below.

The sticking point is that my denominator is different depending on the category being calculated.  I have measures that calculate how many miles "contributed" to the costs in each particular category.

For example, the cost-per-mile for the "Fuel Charges" category above should be based on the _FuelMilesBill immediately above, while the "Lease Expense" category should be divided by _DeprMiles.

Intuitively, this would be a simple IF statement, but I cannot seem to access "billing[charge category]" (or any other text field) as the LogicalTest part of my IF function.  Conceptually speaking, a simplified version of the measure would be something like this:

= SUM(billing[bill_amt]) / if(charge_code_group[charge category]="Fuel Charges",[_FuelMiles],if(charge_code_group[charge category]="Lease Charges",[_DeprMiles],[_UnitMiles]))

I can get the respective mileages to show up in the table, so I know all the data can "see" each other, but I cannot get it to the next level where the cents-per-mile math is applied to the appropriate denominator.

In case it matters, my billing and mileage tables are joined through a key table, where the keys are defined at a vehicle-month level.  (There are many other tables in the data model, but they are unrelated to this problem.)

There's also an enrollment table, defined at a vehicle-month level, which identifies with 1/0 if a vehicle is on a given program or not.  This is 1/0 flag is used to determine the mileages associated with each program.  The structure of the enrollment table is shown here:

In full transparency, this is an example of how the "_XXXMiles" measures are written:

_DeprMiles = CALCULATE(SUM('estimates'[Est Miles]),enrollment[LeaseMonths]=1,all(charge_code_group[charge category]))

I've been scratching my head and googling for ideas for two days, but I'm not closer than I was when I started.  Any help would be much appreciated!

Thanks,

Jacob

[Ashish_Mathur · Accepted solution]

Hi @scotjn1,

Does this work

=IF(HASONEVALUE(charge_code_group[charge category]),SUM(billing[bill_amt]) / if(VALUES(charge_code_group[charge category])="Fuel Charges",[_FuelMiles],if(VALUES(charge_code_group[charge category])="Lease Charges",[_DeprMiles],[_UnitMiles])),BLANK())

[scotjn1]

Thank you!  This has done the trick, and has provided the foundation for this table.  Now if only i could get them to total at the bottom...    It doesn't look like the HASONEVALUE function will allow for that.  Oh, well.  This is good stuff!

[Ashish_Mathur]

You are welcome.  Let me know what figure you are expecting to see in the Grand Total row.  Also, share the link from where i can download your file.

[Greg_Deckler]

I'm thinking that if you are going for a measure, then you will need to do an aggregation on your category like LASTNONBLANK or FIRSTNONBLANK.