Forum Discussion

lrockwell's avatar
lrockwell
Frequent Visitor
9 years ago
Solved

Calculating price per gallon

I have Data from an Excel file.. which i have Merged with Salesforce data. I have other Salesforce data that has Volume added which is a numeric amount referencing how many gallons have been added to the machine. I need to show the $ spend on the fluid by calculating the price per GA times the GA added. How do you go about making it do math? 

Need Help
  • Anonymous's avatar
    Anonymous
    9 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.

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 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.