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

Resolver I

## Cumulative costs

Hi everyone,

I'm trying to visualize cumulative costs a vertical bar chart, by year-month on the X-axis and cumulative costs on the Y-axis.

My TableA has data of CO2 emissions per day, TableB has daily the prices of CO2 emissions per day. Both my TableA and TableB are related to a Calendar table by Date.

Non-cumulative costs I can manage. The CO2 emission allowances are purchased each month, so I'm using the average monthly price of CO2. The sum of CO2 emissions * the average of price works well since both are related to the Calandar table.

When I create a cumulative measure, it adds up the CO2 emissions cumulatively correctly.
The issue is that it also recalculates the price. For example:

January: 5 tons of CO2 * average CO2 price in January of 70 Euro = 350 Euro.

February: 5 tons of CO2 * average CO2 price in February of 60 Euro = 300 Euro.

So the cumulative CO2 emitted = 10 tons, and the cumulative cost should amount to 650 Euro (350 of January + 300 of February).

However for February my DAX recalculates all CO2 emitted with the price of both months, resulting in an incorrect cumulative total cost for 2024-02.
Sample pbix:
https://we.tl/t-jSY5m7tuGy

Any help would be appreciated, thanks!

1 ACCEPTED SOLUTION
Super User

Hi,

PBI file attached.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
5 REPLIES 5
Resolver I

@Ashish_Mathur and @TomMartens  thank you for your replies. I created a sample pbix to clarify the issue:
https://we.tl/t-jSY5m7tuGy

Super User

Hi,

PBI file attached.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Super User

Hey @GKJARC ,

please share the pbix via OneDrive, Google Drive, or Dropbox.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Super User

Hi,

Drag Year and Month name from the Calendar table to the visual.  Assuming, you already have  Cost measure, one of these 2 measures should work?

Measure = SUMX(SUMMARIZE(Calendar,datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date]),calendar[month name],calendar[year],"A",[Cost]),[A])

Measure1 = calculate([Cost],datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date])))

If neither works, then share the download link of the PBI file.  Show the problem there very clearly.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Super User

Hey @GKJARC ,

please create a pbix containing sample data that still reflects your semantic model (tables, relationships, calculated columns, and measures). Upload the pbix file to OneDrive, Google Drive, or Dropbox and share the link. if you are using a spreadsheet to create the sample data instead of the manual input method share the spreadsheet as well.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

## Helpful resources

Announcements

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors