Forum Discussion

joschultz's avatar
joschultz
Icon for Advocate II rankAdvocate II
9 years ago

Change measure based on slicer

 

 

I have two tables.  The first one is Actual sales.

 

DateClientScenarioMetricData
5/1/2017AActualGross Sales500
5/2/2017AActualGross Sales500
5/3/2017AActualGross Sales500
5/4/2017AActualGross Sales500
5/5/2017AActualGross Sales500

 

The second is a forecast table

DateClientScenarioMetricData
5/1/2017APlannedGross Sales1000
5/2/2017APlannedGross Sales1000
5/3/2017APlannedGross Sales1000
5/4/2017APlannedGross Sales1000
5/5/2017APlannedGross Sales1000
5/1/2017ACurrent ForecastGross Sales900
5/2/2017ACurrent ForecastGross Sales900
5/3/2017ACurrent ForecastGross Sales900
5/4/2017ACurrent ForecastGross Sales900
5/5/2017ACurrent ForecastGross Sales900

 

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

Need Help

1 Reply

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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