Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX calculation based on field constraint

I am trying to create a card visual which contains a dynamic calculation - in order to have the results filterable with slicers. 

 

Within the calculation however, there are certain contraints that needs to be applied and I am unsure how to write this in DAX. Based on the data screenshot below i basically need the calculation to do the following:

 

Result = Value (Case = Base & Scenario = 1. None) - Value (Case = Sale & Scenario = 2. Combined)

 

Then I need to use the result to calculate a % change:

 

% Change = Result /  Value (Case = Base & Scenario = 1. None)

 

Any advice on the DAX formula for this? I have tried to use Calculate combined with Filter, however not been able to get it to work.

 

Need Help
  • MFelix's avatar
    MFelix
    7 years ago
    Hi Anonymous,

    Try the following changes:

    Result =
    CALCULATE (
    SUM ( Table[VALUE] );
    Table[Case] = "Base";
    Table[Scenario] = "1. None"
    )
    - CALCULATE (
    SUM ( Table[VALUE] );
    Table[Case] = "Sale";
    Table[Scenario] = "2. Combined"
    )

    The other measure
    % Change = [Result] /
    CALCULATE (
    SUM ( Table[VALUE] );
    Table[Case] = "Base";
    Table[Scenario] = "1. None"
    )


    Regards
    MFelix

4 Replies

  • MFelix's avatar
    MFelix
    Super User
    7 years ago
    Hi ,

    Not on my computer now but your measure should be something like this

    Result =
    CALCULATE (
    SUM ( Table[VALUE] );
    Table[Case] = "Base"
    && Table[Scenario] = "1. None"
    )
    - CALCULATE (
    SUM ( Table[VALUE] );
    Table[Case] = "Sale"
    && Table[Scenario] = "2. Combined"
    )

    The other measure
    % Change = [Result] /
    CALCULATE (
    SUM ( Table[VALUE] );
    Table[Case] = "Base"
    && Table[Scenario] = "1. None"
    )


    Should work.

    Regards
    MFelix
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      MFelix  Thanks - I have used the formula structure as suggested, but I get the following error message:

       

      "The expressions contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression"

      • MFelix's avatar
        MFelix
        Super User
        7 years ago
        Hi Anonymous,

        Try the following changes:

        Result =
        CALCULATE (
        SUM ( Table[VALUE] );
        Table[Case] = "Base";
        Table[Scenario] = "1. None"
        )
        - CALCULATE (
        SUM ( Table[VALUE] );
        Table[Case] = "Sale";
        Table[Scenario] = "2. Combined"
        )

        The other measure
        % Change = [Result] /
        CALCULATE (
        SUM ( Table[VALUE] );
        Table[Case] = "Base";
        Table[Scenario] = "1. None"
        )


        Regards
        MFelix