Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Difference filter instead of calculating differences

I recognize the value of the calculate formula, but am wondering if there's an optimal way to calculate the difference based on specific filtering critera for a data column and then dynamically displ...
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Zach,

     

    I found a new solution without changing your data structure. Please download the demo in the attachment. 

    1. Create a new table. 

    Items	               No.
    Widget Margin	        1
    Widget Waste	        2
    Survey Satisfaction	3
    Widgets Produced	4
    Meetings	        5
    Calls	                6

    2. Create two measures.

    Current Forecast vs. Prior Forecast New =
    SWITCH (
        SELECTEDVALUE ( 'Table3'[No.] ),
        1, CALCULATE (
            SUM ( Table2[Widget Margin] ),
            Table2[Scenario] = "Current Forecast"
        )
            - CALCULATE ( SUM ( Table2[Widget Margin] ), Table2[Scenario] = "Prior Forecast" ),
        2, CALCULATE (
            SUM ( Table2[Widget Waste] ),
            Table2[Scenario] = "Current Forecast"
        )
            - CALCULATE ( SUM ( Table2[Widget Waste] ), Table2[Scenario] = "Prior Forecast" ),
        3, CALCULATE (
            SUM ( Table2[Survey Satisfaction] ),
            Table2[Scenario] = "Current Forecast"
        )
            - CALCULATE (
                SUM ( Table2[Survey Satisfaction] ),
                Table2[Scenario] = "Prior Forecast"
            ),
        4, CALCULATE (
            SUM ( Table2[Widgets Produced] ),
            Table2[Scenario] = "Current Forecast"
        )
            - CALCULATE (
                SUM ( Table2[Widgets Produced] ),
                Table2[Scenario] = "Prior Forecast"
            ),
        5, CALCULATE ( SUM ( Table2[Meetings] ), Table2[Scenario] = "Current Forecast" )
            - CALCULATE ( SUM ( Table2[Meetings] ), Table2[Scenario] = "Prior Forecast" ),
        6, CALCULATE ( SUM ( Table2[Calls] ), Table2[Scenario] = "Current Forecast" )
            - CALCULATE ( SUM ( Table2[Calls] ), Table2[Scenario] = "Prior Forecast" ),
        99999
    )
    
    Forecast vs. Plan New =
    SWITCH (
        SELECTEDVALUE ( 'Table3'[No.] ),
        1, CALCULATE (
            SUM ( Table2[Widget Margin] ),
            Table2[Scenario] = "Current Forecast"
        )
            - CALCULATE ( SUM ( Table2[Widget Margin] ), Table2[Scenario] = "Plan" ),
        2, CALCULATE (
            SUM ( Table2[Widget Waste] ),
            Table2[Scenario] = "Current Forecast"
        )
            - CALCULATE ( SUM ( Table2[Widget Waste] ), Table2[Scenario] = "Plan" ),
        3, CALCULATE (
            SUM ( Table2[Survey Satisfaction] ),
            Table2[Scenario] = "Current Forecast"
        )
            - CALCULATE ( SUM ( Table2[Survey Satisfaction] ), Table2[Scenario] = "Plan" ),
        4, CALCULATE (
            SUM ( Table2[Widgets Produced] ),
            Table2[Scenario] = "Current Forecast"
        )
            - CALCULATE ( SUM ( Table2[Widgets Produced] ), Table2[Scenario] = "Plan" ),
        5, CALCULATE ( SUM ( Table2[Meetings] ), Table2[Scenario] = "Current Forecast" )
            - CALCULATE ( SUM ( Table2[Meetings] ), Table2[Scenario] = "Plan" ),
        6, CALCULATE ( SUM ( Table2[Calls] ), Table2[Scenario] = "Current Forecast" )
            - CALCULATE ( SUM ( Table2[Calls] ), Table2[Scenario] = "Plan" ),
        99999
    )
    

    3. Now you can format the old table as you wish.

    Difference-filter-instead-of-calculating-differences2

     

    Best Regards,
    Dale