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...
Need Help
  • 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

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

Hi Anonymous,

 

I would suggest you transform the data structure that makes the calculation easily. Please download the demo in the attachment.

Current Forecast vs. Prior Forecast =
SUM ( Table1[Current Forecast] ) - SUM ( Table1[Prior Forecast] )

Forecast vs. Plan = sum(Table1[Current Forecast]) - sum(Table1[Plan])

Difference-filter-instead-of-calculating-differences

 

Best Regards,
Dale

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi v-jiascu-msft,

 

Thank you for your feedback.  As one follow-up to your solution, do you have any recommendations if I wanted to display 'Widget Margin' and 'Widget Waste' as a percent?  It's my understanding that the data for these attributes would have to be reflected in separate data columns if I wanted to display them as at a % instead of the decimals that you've defined for these 3 data columns.  My goal is to limit the number of variance columns that are created, but have the flexibility to display certain attributes as different data types (such as %'s).

 

Thanks,

 

Zach

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    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