cancel
Showing results 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

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.

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.

Announcements

#### 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 Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors