The (ALL, ALLSELECTED & ALLEXCEPT) Code

[vivran22]

For someone utterly new to Power BI, and especially for those who have been using Excel, understanding DAX can be an overwhelming experience. At least, it was for me. It took me some time to grasp the concepts, filter and row contexts, and the CALCULATE function.

One of the standard requirements in a BI report is calculating percentages.
Let us consider simple visual showing orders by Product Category and percentage contribution:

Achieving this is a reasonably easy task in Excel, and also when using simple implicit measures in Power BI

So, how could we achieve the same using measures (DAX)? Well, this depends on how we want to see our results.

Objective 1: Calculating the percentage of the total (ALL)

For calculating the percentage of the total, here is the formula: Total Orders by each Product / Total Orders

Let’s start with writing a simple measure for Total Orders:

Total Orders = SUM(Orders[Order Quantity])

50% of the objective accomplished. Yay!!

Now for the most uncomplicated part:

There are two evaluation contexts in Power BI/Tabula model: Filter Context and Row Context. Evaluation of each DAX expressions happens inside a context.

A more natural way of understanding a filter context: In this example, when we add Product Category to the visual, it filters the Order table by each product category, and then calculate the sum of Order Quantity. A separate blog covers these contexts in detail.

The denominator of the ratio needs to ignore the existing filter context for calculating the grand total (or simply put, we need ALL the Product Categories). So, our formula becomes:

All Orders = 
CALCULATE(
    [Total Orders],
    ALL(Orders)
)

In simple English, we are asking DAX to:

• Remove all the external filter contexts applied by the visual (Product Category)
• Calculate the sum of the Order Quantity of the visible columns.

So, we have the numerator and denominator. The following measure calculates the required percentage:

Order Pct (ALL) = 

//Calculating the numerator
VAR _Orders = 
    [Total Orders] -- This measure calculates orders withing the filter context

//Calculating the denominator by removing any filter context
VAR _AllOrders = 
    CALCULATE(
        [Total Orders],
        ALL(Orders) –- Considers the entire Order table
    )

//Calculating the ratio
VAR _OrderPct = 
    DIVIDE(_Orders, _AllOrders)

RETURN

_OrderPct

And calling the measure in the visual:

Objective 2: Consider the filters applied to the visual (ALLSELECTED)

Let’s take one step further. We included a slicer in our report, and we wish to update the denominator according to the selection. We have learned in the previous segment; ALL removes all the external filters applied on the table during the calculation. The same is evident in the example below:

The value of total orders remained unchanged even when additional filters were applied using a slicer.
For this, we made one small adjustment while calculating the denominator:

AllSelected Orders = 
CALCULATE(
    [Total Orders],
    ALLSELECTED(Orders[Product Category]) -- Considers the filters applied by selecting any visual
)

In simple English, we are asking DAX to:

• Remove all the external filter contexts applied by the visual (Product Category)
• Filter the Product Category column in the Orders table on the selected values (Furniture & Office Supplies in this case)
• Calculate the sum of the Order Quantity of the visible columns.

Next step is to calculate the percentage:

Order Pct (ALLSELECTED) = 
        DIVIDE([Total Orders], [AllSelected Orders])

Objective 3: How to calculate the percentage of the parent total? (ALLEXCEPT)

When dealing with hierarchical data, the requirement is to calculate % of the parent total:

In the example above, there are two levels of filter contexts applied:

• Level 1: Product Category
• Level 2: Product Sub-Category

The objective is to keep filters at Level 1 and not at Level 2. ALLEXCEPT comes to the rescue:

AllExcept Orders = 
CALCULATE(
    [Total Orders],
    ALLEXCEPT(Orders,Orders[Product Category])
)

In simple English, we are asking DAX to:

• Remove all the external filter contexts applied by the visual (Product Category & Sub-Category)
• Filter the Product Category column in the Orders table on the Product Category only
• Calculate the sum of the Order Quantity of the visible columns.

And then we use the DIVIDE function to calculate the percent contribution:

Order Pct (ALLEXCEPT) = 
        DIVIDE([Total Orders], [AllExcept Orders])

In conclusion, if we know how we want to filter our data table and take the help of appropriate DAX, calculating the percentage of the total is not that complicated.

Easy when you know how.

[Anonymous]

Thank you for this explanation!  

[SimonCapelin]

A very valuable contribution, thank you.

[Chirag4370]

Thanks @vivran22  for Very Helpfull, Explanation. 

I have one question : How can we use ALLEXCEPT when more then two level of filter context applied? means, if there is Product Sub- Sub category (level 3). 

I want percentage as shown in LCarat Percentage column. 

Article	Memo	SubLot	sSubLot	Lcarat	LCarat Percentage
aa	aa1	a	1	10	45.45%
		a	1	12	54.55%
	SubTotal			22	28.95%
		a	2	26	48.15%
		a	2	28	51.85%
	SubTotal			54	71.05%
	Total			76	9.61%
bb	bb1	a	1	110	47.83%
		a	1	120	52.17%
	SubTotal			230	302.63%
		a	2	260	53.61%
		a	2	225	46.39%
	SubTotal			485	67.83%
	Total			715	90.39%
GrandTotal				791	100%

above table is output of Excel using Percentage of Parant Row Total in pivot table.

[beefbriskets0up]

This is the best explanation and set of examples I've ever seen about ALLEXCEPT and ALLSELECTED, not verbose, you've let well selected examples based on real world use cases do the explaining.