Forum Discussion

Sureshot's avatar
Sureshot
Icon for Helper I rankHelper 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...
  • 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

Stachu's avatar
Stachu
Icon for Community Champion rankCommunity 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
Icon for Helper I rankHelper 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
    Icon for Community Champion rankCommunity 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

    • Sureshot's avatar
      Sureshot
      Icon for Helper I rankHelper I
      7 years ago

      That worked - you're a genius!