Forum Discussion
Anonymous
7 years agoNot applicable
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...
- 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.
Best Regards,
Dale
Anonymous
7 years agoNot applicable
I have approximately 50 columns of unique factual data and am wondering if there's a way to create variance calculations that can be used across each of these columns. The variance calculations would be created based off of the dimensional attributes such as Current Forecast versus Prior Forecast, or Business A vs Businss.