DAX: Cumulative Total Applying filters

Question

Hi,&nbsp;I was trying to get the cumulative total&nbsp;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.&nbsp;&nbsp;For example:&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Total&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Cumulative&nbsp;Total2010&nbsp; &nbsp; &nbsp; 81$&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;81$2011&nbsp; &nbsp; &nbsp; 83$&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 164$&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2012&nbsp; &nbsp; &nbsp; 90$&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;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.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Total&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Filtered Cumulative&nbsp;Total2011&nbsp; &nbsp; &nbsp; 83$&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 83$&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2012&nbsp; &nbsp; &nbsp; 90$&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;173$I have tried the following DAX formulas, but always is returning the cumulative taking the whole dataset.&nbsp;&nbsp;Cumulative Total Applying filters = CALCULATE([Total Sales]; FILTER(ALLEXCEPT('Calendar';'Calendar'[Date].[Year]); 'Calendar'[Date] &lt;= MAX('Calendar'[Date])))&nbsp;&nbsp;&nbsp;Cumulative Total Applying filters = CALCULATE([Total Sales]; FILTER(ALLSELECTED('Calendar'); 'Calendar'[Date] &lt;= MAX('Calendar'[Date])))&nbsp;Any idea?&nbsp; Please find attached an example of PowerBI where I've tried these formulas.&nbsp;DAX ExampleThanks!&nbsp; &nbsp;

popov · Answer

Hi,&nbsp;glaso-isIf I correct understud, you need to use ALLSELECTED() function, something like this:Cumulative Total Applying filters :=CALCULATE (&nbsp;&nbsp;&nbsp; [Total Sales];&nbsp;&nbsp;&nbsp; FILTER (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ALLSELECTED ( 'Calendar' );&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Calendar'[Date] &lt;= MAX ( 'Calendar'[Date] )&nbsp;&nbsp;&nbsp; ))

greg_deckler · Answer

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.&nbsp;

glaso-is · Answer

"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.

glaso-is · Answer

Hi&nbsp;popov,&nbsp;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 20112013: Sum Total of 2011 and Total 2013&nbsp;However, using the formulas that I have posted in my first post I obtain the following results:2011:&nbsp; Sum Total 2010 and Total 20112013:&nbsp; Sum Total 2010, Total 2011, Total 2012 and Total of 2013&nbsp;Here, PBIX file where I have reproduced the problem:https://www.dropbox.com/s/rurt5vp7ddbcs87/Test%20Cumulative%20Total%20Applying%20filters.pbix?dl=0&nbsp;Thanks!

popov · Answer

Hi,&nbsp;glaso-isI use formula in my previos post and getting correct result. See screenshot. In your file, formula of&nbsp;Cumulative Total Applying filters is not correct.&nbsp;

Forum Discussion

DAX: Cumulative Total Applying filters

7 Replies

Related content

Cumulative Total

Apply data to all rows in a filtered column

Apply Filter before Calculating Average

Creating Cumulative Graphs

Cumulative total more than current value using current filters