Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have two tables. The first one is Actual sales.
Date | Client | Scenario | Metric | Data |
5/1/2017 | A | Actual | Gross Sales | 500 |
5/2/2017 | A | Actual | Gross Sales | 500 |
5/3/2017 | A | Actual | Gross Sales | 500 |
5/4/2017 | A | Actual | Gross Sales | 500 |
5/5/2017 | A | Actual | Gross Sales | 500 |
The second is a forecast table
Date | Client | Scenario | Metric | Data |
5/1/2017 | A | Planned | Gross Sales | 1000 |
5/2/2017 | A | Planned | Gross Sales | 1000 |
5/3/2017 | A | Planned | Gross Sales | 1000 |
5/4/2017 | A | Planned | Gross Sales | 1000 |
5/5/2017 | A | Planned | Gross Sales | 1000 |
5/1/2017 | A | Current Forecast | Gross Sales | 900 |
5/2/2017 | A | Current Forecast | Gross Sales | 900 |
5/3/2017 | A | Current Forecast | Gross Sales | 900 |
5/4/2017 | A | Current Forecast | Gross Sales | 900 |
5/5/2017 | A | Current Forecast | Gross Sales | 900 |
In the second table there are two types of forecasts. One is Planned and the other is Current Forecast. I have measures created for all three. However I want to be able to have a slicer on my reports that lets me change my calcuations based on Planned and Current Forecast. For instance I want my MTD variance to be [Total Actual] - [Total Planned or Current Forecast} based on which one I have selected.
Any ideas on the best way to do this?
Thank you,
Joseph
Hi Joseph,
First, we need a Date table and then establish relationships with ActualSales and ForcastSales. This can ensure we evaluate the MTD in the same time period. We can create a Date table with this formula.
Date = CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2017, 12, 31 ) )
Actually, we just need one measure. But we create three to see the steps.
TotalActual = TOTALMTD ( SUM ( ActualSales[Data] ), 'Date'[Date] )
TotalForcast = TOTALMTD ( SUM ( ForecastSales[Data] ), 'Date'[Date] )
Result = TOTALMTD ( SUM ( ActualSales[Data] ), 'Date'[Date] ) - TOTALMTD ( SUM ( ForecastSales[Data] ), 'Date'[Date] )
or
Result = [TotalActual] - [TotalForcast]
Finally we can create a slicer with ForcastSales[scenario]. And a slicer with 'Date'[date] if needed.
Best Regards!
Dale
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |