Power Pivot Principles: ALLSELECTED

Welcome back to our Power Pivot blog. Today we discuss how to use the ALLSELECTED function in DAX.

If you have been keeping up with our blogs, you will probably be au fait with creating a PivotTable that looks something like this:

You may download our sample PivotTable here.  Let’s do something here that is actually quite awkward to code in Excel.  We’ll start by creating the following measure:

Our PivotTable may look like this now:

Big deal, you might say – this is akin to the ALL function discussed recently here. It’s not though; it is a big deal. To demonstrate, let’s also insert a slicer for the months (i.e. the field that is the subject of the ALLEXCEPT function):

Choosing months on the slicer will result in the PivotTable recalculating the total sales for the months selected:

This only considers the months selected, not all months. Now that is not straightforward in Excel, especially if we were to apply ALLEXCEPT to a field that does not appear in the PivotTable.

For le coup de grâce, let’s create the following measure:

If you don’t know what the BLANK() function is in there for you can read about it in this blog.

Our PivotTable will now have different weightings of sale percentages depending on our selection of months and suppress any missing data / zero values:

That’s it for this week, stay tuned to our blog for more on Power Pivot. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.