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

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!