Forum Discussion

Coffee321's avatar
Coffee321
Frequent Visitor
2 years ago
Solved

Switch Measure in Matrix with multiple columns

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.



 

Need Help
  • vigneshba's avatar
    vigneshba
    2 years ago

    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

     

3 Replies

    • Coffee321's avatar
      Coffee321
      Frequent Visitor
      2 years ago

      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:

       







  • vigneshba's avatar
    vigneshba
    Frequent Visitor
    2 years ago

    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