Forum Discussion

Sureshot's avatar
Sureshot
Helper I
7 years ago
Solved

DAX ALL() not working

 

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's avatar
    Stachu
    7 years ago

    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

4 Replies

  • Stachu's avatar
    Stachu
    Community Champion
    7 years ago

    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's avatar
      Sureshot
      Helper I
      7 years ago

      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's avatar
        Stachu
        Community Champion
        7 years ago

        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