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.
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
Solved! Go to 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())
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())
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!
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |