Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
87 | |
84 | |
65 | |
49 |
User | Count |
---|---|
140 | |
114 | |
110 | |
59 | |
59 |