Change measure based on slicer

[joschultz]

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

[v-jiascu-msft]

@joschultz

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