DAX ALL() not working

[Sureshot]

Hi.

I have a Matrix with 4 columns as follows:

Line Of Business - Current Year - % of Total - % Division

Liability               - $47,993,349  - 63%          - 63%

Property              - $27,765,694  - 36%          - 36%

Umbrella             - $358,047       -  0%           -  0%

Total                    - $76,117,090  - 100%        -  100%

When I filter by a specific agent I get:

Line Of Business - Current Year - % of Total - % Division

Liability               - $661,413      - 49%             - 1%

Property              - $695,683      - 51%             - 1%

Umbrella             - $2,826           -  0%             -  0%

Total                    - $1,359,922    - 100%          -  2%

The FACT Table is the source for the Current Year value.

The FACT table is filtered by an Agent Dimension and both are joined on AgentCode.

I need the % Division field to not change when the Agent Filter is applied (in fact when any dimension filter is applied). 

Here is my DAX but the ALL() does not ignore the filters.

% Division = DIVIDE(SUM('Agency Scorecard 3a'[Business DWP]), CALCULATE(SUM('Agency Scorecard 3a'[Business DWP]), ALL()),0)

Can anyone help?

[Stachu]

ALL works exxectly as designed here - it does remove all the filters, including the one you put on the specific agent

it basically does 

661,413/7,611,709 ~= 1% 
agent specific sales divided by total sales

rather than what you want

661,413/1,359,922 ~=49%
agent specific sales divided by agent specific total sales

you may want to use ALLSELECTED or ALLEXCEPT instead

also you can have a read here
https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...
https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/

[Sureshot]

Hi.

Thanks for your response but what I want is for % Division to stay at 63% & 36% regardless of what filter is applied, in this case agent.

Is there a way to ignore all filters or specify what filters to ignore in order to satisfy my requirement?

I've tried ALL, ALLSELECTED and ALLEXCEPT

Thanks.

[Stachu · Accepted solution]

Right now you only remove filters for the denominator, for nominator you want to remove all filters except for the Line of Business

e.g. like this

% Division =
VAR __Nominator =
    CALCULATE (
        SUM ( 'Agency Scorecard 3a'[Business DWP] ),
        ALLEXCEPT ( 'Agency Scorecard 3a', 'Agency Scorecard 3a'[Line Of Business] )
    )
VAR __Denominator =
    CALCULATE ( SUM ( 'Agency Scorecard 3a'[Business DWP] ), ALL () )
RETURN
    DIVIDE ( __Nominator, __Denominator, 0 )

you may need to adjust the table name for the nominator

[Sureshot]

That worked - you're a genius!