Switch Measure in Matrix with multiple columns

[Coffee321]

What's the best way to switch a measure in a matrix with multiple columns? The below matrix uses fours different measures based on amounts (dollars):

[Actuals] = Measure of the current period's amounts in dollars
[Actuals PP] = Measure of the previous period amounts in dollars
[Actuals Var $] = Measure of Variance in dollars
[Actuals Var %] = Measure of Variance in percentage

I would like to show all columns but be able to switch the base of all measures between the following:

- dollars (all 4 measures mentioned above are based on this)
- volume (quantities of units, which is a separate column in the fact table in addition to dollars) and,
- rate/price (dollars divided by quantity)

In other words, show 4 columns of actuals, prior period, variance in $, and variance in % and be able to switch between dollars, quantity, and rate/price. I can do this now, but I have to take out all 4 measures mentioned above manually and put four measures based on volume and do the same but for rate/price. Would a calculation group allow me to do this (switch between dollars, volume, and quantity) by just using a slicer? Any help will be greatly appreciated.

[Ritaf1983 · Accepted solution]

Hi @Coffee321 
It sounds like field parameters, please check the attached link :
https://www.youtube.com/watch?v=V6WchPDZibI&t=3s

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[vigneshba · Accepted solution]

Hi @Coffee321 - This can be done by following approach

1. We have to create table with the values to be listed in the Filter. Dollars, Volume, Per Unit
2. Create 4 Measurements as below

a. Actuals = SWITCH(SELECTEDVALUE('Filter'[Measure]), "Dollars", SUM(Data[Actuals $]), "Volume", SUM(Data[Actuals Vol]), "Per Unit", SUM(Data[Actuals Per Unit]), BLANK())

b. Actuals PP = SWITCH(SELECTEDVALUE('Filter'[Measure]), "Dollars", SUM(Data[Actuals PP $]), "Volume", SUM(Data[Actuals PP Vol]), "Per Unit", SUM(Data[Actuals PP Per Unit]), BLANK())

c. Var = [Actuals] - [Actuals PP]

d. Var % = (ABS([Actuals]) - ABS([Actuals PP])) / ABS(([Actuals]))

In this way, we can achieve your requirement. Let me know if you want more details or any related questions

[Ritaf1983 · Accepted solution]

Hi @Coffee321 
It sounds like field parameters, please check the attached link :
https://www.youtube.com/watch?v=V6WchPDZibI&t=3s

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[Coffee321]

Thanks. I don't think that will work because I will need 12 different parameters in a slicer and I will be able to only look at one column at a time in the matrix. What I'm after is this:

Slicer with three options:

And that slicer, filtering the matrix like this:

When selecting dollars, all 4 columns (4 measures) will show amounts in dollars

When selecting volume, all 4 columns (measures) will show measures based on volumes:

When selecting Per Unit, all 4 columns will show per unit: