Forum Discussion
Calculating price per gallon
- Anonymous9 years ago
What you need to do is create a Measure to do all of this. You will need to make use of "SUM" functions to take the aggregate values in the table and arrive a single total figure. If your pricing information is in a differnet place to your fuel information you will need to make sure your tables are linked.
Lastly if your pricing information changes from Time to Time, you will need a Date table that you can leverage off. Using the Values function you will be able to run your Sums on a period by period basis getting the correct totals for that single period. This could look like:
Fuel Price = if( COUNTROWS(values('Dim - Date Table'[YearMonth])) = 1, SUM('FuelTable'[FuelAmount]) * MEDIAN('Pricing Table'[Price]), SUMX( VALUES('Dim - Date Table'[YearMonth]), SUM('FuelTable'[FuelAmount]) * MEDIAN('Pricing Table'[Price]) ) )This code would be attempting to multiply the sum of fuel of a given month by the median price of that month. Not the only way to do it, but hopefully enough to give you a picture.
What you need to do is create a Measure to do all of this. You will need to make use of "SUM" functions to take the aggregate values in the table and arrive a single total figure. If your pricing information is in a differnet place to your fuel information you will need to make sure your tables are linked.
Lastly if your pricing information changes from Time to Time, you will need a Date table that you can leverage off. Using the Values function you will be able to run your Sums on a period by period basis getting the correct totals for that single period. This could look like:
Fuel Price = if(
COUNTROWS(values('Dim - Date Table'[YearMonth])) = 1,
SUM('FuelTable'[FuelAmount]) * MEDIAN('Pricing Table'[Price]),
SUMX(
VALUES('Dim - Date Table'[YearMonth]),
SUM('FuelTable'[FuelAmount]) * MEDIAN('Pricing Table'[Price])
)
)This code would be attempting to multiply the sum of fuel of a given month by the median price of that month. Not the only way to do it, but hopefully enough to give you a picture.