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

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!