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
glaso-is
Frequent Visitor

DAX: Cumulative Total Applying filters

Hi,

 

I was trying to get the cumulative total from a filtered dataset selecting some years in the slicer, but I'm always obtaining the cumulative from the whole dataset only for the selected years. 

 

For example:

 

              Total          Cumulative Total
2010      81$                     81$

2011      83$                    164$           

2012      90$                     254$


If I select 2011 and 2012 I would like to obtain the cumulative only for the selected years and not for the entire dataset.

              Total         Filtered Cumulative Total

2011      83$                    83$           

2012      90$                   173$

I have tried the following DAX formulas, but always is returning the cumulative taking the whole dataset.

 

 

Cumulative Total Applying filters = CALCULATE([Total Sales]; FILTER(ALLEXCEPT('Calendar';'Calendar'[Date].[Year]); 'Calendar'[Date] <= MAX('Calendar'[Date])))

 

 

 

Cumulative Total Applying filters = CALCULATE([Total Sales]; FILTER(ALLSELECTED('Calendar'); 'Calendar'[Date] <= MAX('Calendar'[Date])))

 


Any idea?  Please find attached an example of PowerBI where I've tried these formulas. DAX Example

Thanks!
   

7 REPLIES 7
Greg_Deckler
Super User
Super User

Is "Total" a column in your example a column? The default SUM aggregation of the column will work in your case for the data you provided. 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

"Total" is a measure, a simple of a sum of a column. The problem is the cumulative is always taking the previous years, ignoring the slicer.  Please find a link of PBIX file where I have reproduced my problem:  

 

https://www.dropbox.com/s/rurt5vp7ddbcs87/Test%20Cumulative%20Total%20Applying%20filters.pbix?dl=0

 

Thanks.

Hi, @glaso-is
If I correct understud, you need to use ALLSELECTED() function, something like this:

Cumulative Total Applying filters :=
CALCULATE (
    [Total Sales];
    FILTER (
        ALLSELECTED ( 'Calendar' );
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)

MKY
Frequent Visitor

Function 'ALLSELECTED' made lot of difference in one of the database I was working with. I was using 'ALL' but when replaced with 'ALLSELECTED' the issue was resolved. Thanks a lot.

Anonymous
Not applicable

Thank you!!!

Hi @popov,

 

The DAX formula that you commented is one of Ithe formulas that I'd tried as I mentioned in my first post, but the problem is always adding the previous years. For example, if I have a dataset with data for 2010, 2011,2012,2013 and I select in the slicer 2011 and 2013 my expectations is to obtain the following:


2011: Sum Total of 2011
2013: Sum Total of 2011 and Total 2013

 

However, using the formulas that I have posted in my first post I obtain the following results:

2011:  Sum Total 2010 and Total 2011

2013:  Sum Total 2010, Total 2011, Total 2012 and Total of 2013

 

Here, PBIX file where I have reproduced the problem:

https://www.dropbox.com/s/rurt5vp7ddbcs87/Test%20Cumulative%20Total%20Applying%20filters.pbix?dl=0

 

Thanks!

Hi, @glaso-is
I use formula in my previos post and getting correct result. See screenshot. In your file, formula of Cumulative Total Applying filters is not correct. Running Total.png

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.

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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