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
GKJARC
Resolver I
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

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1716247772680.png

 


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

View solution in original post

5 REPLIES 5
GKJARC
Resolver I
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

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1716247772680.png

 


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

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
Ashish_Mathur
Super User
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/
TomMartens
Super User
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
Europe Fabric Conference

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors