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
Yazid
Helper I
Helper I

Cumulative sum by taking into account slicers

Dear,

My data:

Yazid_2-1716902980900.png

 

Here is my issue, I have a table ['MERGED-COST SAVINGS'] with differents columns (i.e granularity) and i want to RETURN a colum G with the cumulative sum of the savings (Column F) in function of the date (Column E) 

 

I wrote this formula: 

Yazid_0-1716902666153.png

 

Using this, I got the wanted graph, see below:

Yazid_1-1716902827782.png

 

However, when I'm applying any slicer on this graph, for example by SITE or TYPE or SAVING IDEA,

 

then the curves and the amount ARE NO LONGER CORRECT.

 

How can I still have my graphs and amount correct AFTER applying any slicer ?

 

Thanks in advance for your support !

 

1 ACCEPTED SOLUTION
sjoerdvn
Super User
Super User

Well, this not Excel.
Assuming that start date is NOT in a relationship with a date dimension table, try this:

 

Cumulative Sum = VAR ed=MAX('MERGED-COST SAVINGS'[Start date])
RETURN CALCULATE(SUM('MERGED-COST SAVINGS'[Full Year Forecast 2024]), ALL('MERGED-COST SAVINGS'[Start date]), 'MERGED-COST SAVINGS'[Start date]<=ed)

 

View solution in original post

7 REPLIES 7
sjoerdvn
Super User
Super User

Forget about previous rows, this is not Excel. Cummulative values we do all the time in Power BI. As I do not have your pbix it is hard to guess what;s going on, but if you're using a date dimension in your visual that is filtering that fact table you should use correct the measure to use the date dimension.

sjoerdvn
Super User
Super User

Well, this not Excel.
Assuming that start date is NOT in a relationship with a date dimension table, try this:

 

Cumulative Sum = VAR ed=MAX('MERGED-COST SAVINGS'[Start date])
RETURN CALCULATE(SUM('MERGED-COST SAVINGS'[Full Year Forecast 2024]), ALL('MERGED-COST SAVINGS'[Start date]), 'MERGED-COST SAVINGS'[Start date]<=ed)

 

Dear,

Thx for your reply. However, I still have the same results than the measure I've posted.

 

My goal is to create a physical colum on Dax based on Excel example here below:

Yazid_0-1716907817753.png

By doing so, I would have a colum, which a each step (each incremented date), I have the cummulative sum.

 

Then, I would just need to plot such colum and I will have my raising curve in fct of time.

sjoerdvn
Super User
Super User

My guess is that you've created a computed column where you should create a measure. Computed columns are calculated when loading the model and are never affected by filters & slicers....

Dear,

 

Thx for your reply.

 

I did create a measure, see screenchot.

 

Yazid_0-1716904120782.png

 

Ah OK.
The problem in your measure is most likely the ALL() function overriding all filters, you would want to override only the start date context.

Basicaly,

 I want to create (add) a column such as below:

Yazid_0-1716904797593.png

 

This is easy to do on Excel:

for a date (t), I sum its corresponding amount + the amount on date (t-1)

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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