Create DAX to Calculate "% Grand Total" that can Dynamically Change According to Filter

[BoonPin]

Data Set:

Expectation 1: With All Data. The percentage is shown as per Visual 1.

Visual 1

Expectation 2: Product Group 3 is filtered. The percentage should be shown as per Visual 2.

Visual 2

Query: Is it possible to do it with DAX? I think for Visual 1 is possible with REMOVEFILTERS()

I know we can do it with "Show value as > Percent of grand total". I would like to know the other alternative methods to achieve the same result.

[BoonPin]

The DAX is working for Visual 1 but not Visual 2. The total % of Visual 2 is not equal 100% if i am using the same DAX. The Visual 2 example that provided above will sum to 100%. 

For Visual 2, the denumerator will exclude Product Group 3's QTY. So the total QTY is 33. Eg, Product A % would be 12.12%. 

My understanding for ALL() function is that the function will take all QTY including the filtered Product Group. Please correct me if I am wrong. 

[rubayatyasmin · Accepted solution]

@BoonPin 

you are correct. Use ALLSELECTED instead of ALL. This will work. 

Working DAX,

% of Grand Total =

DIVIDE(

    SUM(Table1[qty]),

    CALCULATE(SUM(Table1[qty]), ALLSELECTED(Table1)

)

)

[rubayatyasmin]

Hi, @BoonPin 

use this dax, this shows exact values that you will get after using 'show value as percentage of grand total' 

% of Grand Total =

DIVIDE(

    SUM(Table1[qty]),

    CALCULATE(SUM(Table1[qty]), ALL(Table1))

)