Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
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.
"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] )
)
)
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.
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.
User | Count |
---|---|
134 | |
71 | |
70 | |
54 | |
52 |
User | Count |
---|---|
205 | |
95 | |
63 | |
61 | |
54 |