Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
lrockwell
Frequent Visitor

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? 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

 

View solution in original post

1 REPLY 1
Anonymous
Not applicable

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.

 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors