I rarely use ALLSELECTED(). Last time I did, I had to go look at the MSDN docs, never a good sign. But I have to say, unlike some of the docs… this one seems both accurate and understandable!
I decided to play with it a bit today; here are the measures I tested with:
Total Intensity:=SUM(Colors[Intensity])
Total Intensity, ALL Level:=CALCULATE([Total Intensity], ALL(Colors[Level]))
Total Intensity – ALLSELECTED Level:=CALCULATE([Total Intensity], ALLSELECTED(Colors[Level]))
And here is my test pivot before I sliced anything. Notice both ALL and ALLSELCTED are showing the same thing (just clearing the filter context on the Level field).
But watch what happens when I select just the Awesome and Lame levels:
As expected, the ALL() is unchanged. However… the ALLSELECTED is now showing the total of (just) all the Awesome and Lame values (notice the 4954.0 in the Total Intensity Grand Total … same in all rows of ALLSELECTED column).
In this way, the measure using ALLSELECTED is returning the “Visual Total”. As the MSDN definition says, ALLSELECTED is removing ONLY filters created by the rows/columns of the pivot table. All other filters are respected. We saw the Slicers respected here, but I also tested with report filters and Value/Label filters (like Top 10 and Greater Than), and they behaved like slicers. And for clarity, ALL() on the other hand doesn’t care where the filter came from… it is gone.
A pretty useful tool in your belt, in my opinion, especially when you want these types of “visual totals” or “% of Visual” measures.
Thanks for the post. I’ve noticed that when I remove all the filters for a slicer it breaks the ALLSELECTED functionality. For example, if I use it to calculate a percentage, it will assign 100% to sub-levels within the visible total.
Is this a known issue?
Thanks,
Ben
Hi Ben, sorry for the delayed reply. I don’t know of any issues here, and fear I am not totally getting you. In my example above, when Level has nothing sliced (so, no filters) — my measure is returning the total of all rows — which is what I would expect.
If you want to send me a sample workbook, or jump over to the Mr Excel powerbi forums… I am sure we can get to the bottom of this 🙂