Dynamic measures with two slicers

[Jouni900]

Hi,

I have a dashboard where there is a matrix visual and a stacked bar chart in a time series format.  My measures are all related to volume and I have a slicer for the matrix where there are two selections: 1) Monthly and 2) YTD. For the time series chart I have a slicer where there is possible to choose against which benchmark the current volume is being measured. In my case there are three selections: against previous year, budget and a forecast on a monthly level.

I would like to improve the content of the report in the following way:

Matrix visual: I would have a slicer with two selections "Volume" and "Sales". If I choose either of them, then using the existing slicer, I can furthermore choose whether I want to look it on a Monthly or YTD level. 

Time series: The " Volume" & "Sales" slicer would change the unit of measure and then I could look e.g. Sales agains budget or previous year as I currently do with volume.

Is this possible to implement? I could of course add the Sales as an option to the current slicers selections but is there other way where I would simply could add a new slicer where I have Volume and Sales as options and based on the selection the measures in the visuals would change accordingly?

[Sahir_Maharaj]

Hello @Jouni900,

1. Yes, you would need to duplicate the line for all measures that you have. For example, you could create separate measures for Actuals, To PY, Budget, and Forecast, each with its own IF statement based on the selected value of the "Volume" or "Sales" slicer.

2.  To connect the "Volume" and "Sales" selection to the other slicer where you have a selection between "Monthly and YTD", you could create a measure that uses the SWITCH function to switch between the different measures based on the selected values of both slicers:

Selected Measure = 
VAR VolumeSelected = SELECTEDVALUE('Slicer'[Selection])
VAR TimeSelected = SELECTEDVALUE('TimeSlicer'[Selection])
RETURN
SWITCH(TRUE(),
    VolumeSelected = "Volume" && TimeSelected = "Monthly", [Monthly Volume Measure],
    VolumeSelected = "Volume" && TimeSelected = "YTD", [YTD Volume Measure],
    VolumeSelected = "Sales" && TimeSelected = "Monthly", [Monthly Sales Measure],
    VolumeSelected = "Sales" && TimeSelected = "YTD", [YTD Sales Measure],
    BLANK()
)

Let me know if you might need further assistance!

[Sahir_Maharaj]

Hello @Jouni900,

To do this, you will need to create two new measures, one for "Volume" and one for "Sales". These measures will use the same underlying data as your existing measures, but will be calculated differently depending on the selection in the new slicer.

Volume = IF(SELECTEDVALUE('Slicer'[Selection]) = "Volume", [Existing Volume Measure], BLANK())

Sales = IF(SELECTEDVALUE('Slicer'[Selection]) = "Sales", [Existing Sales Measure], BLANK())

Once you have created these measures, you can use them in your visuals instead of the existing volume and sales measures. You can also use the new slicer to control which measure is displayed in each visual.

For the time series chart, you can use the same approach to create measures for each benchmark (e.g. "Previous Year Sales", "Budget Sales", "Forecast Sales"). You can then use a switch statement in your visual to select the appropriate measure based on the selection in the benchmark slicer.

This will give you more flexibility in how you display your data, and will make it easier to switch between different measures and benchmarks.

Let me know if this works for you.

[Jouni900]

Hi, @Sahir_Maharaj 

Thanks for the reply, I was able to progress a bit with it. I have couple follow up questions about the matrix visual

1) I have measures such as Actuals, to PY etc in the matrix. Should I duplicate the line for all measures that I have? 

Volume = IF(SELECTEDVALUE('Slicer'[Selection]) = "Volume", [Existing Volume Measure], BLANK())

for Actuals, to PY etc.? 

2) How do I connect the "Volume" and "Sales" selection to the other slicer where I have a selection between "Monthly and YTD". I would like that all the measures that I have change to YTD when changed so, so I would not need to include "YTD" measures separately to the matrix.

My current solution for the Act is a following. What I am missing at this point is how to tie it to the "Volume" and "Sales" selection

Measures = SWITCH(TRUE(),"Monthly Vol" IN ALLSELECTED('Dashboard table measures'[Selection]),[Total Vol Blank],"YTD Vol" IN ALLSELECTED('Dashboard table measures'[Selection]),[YTD Vol Blank])

I appreciate your help!