cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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.

2 ACCEPTED SOLUTIONS
Super User

Hi @Coffee321

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

Regards,
Rita Fainshtein | Microsoft MVP
Frequent Visitor

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 3
Frequent Visitor

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

Super User

Hi @Coffee321

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

Regards,
Rita Fainshtein | Microsoft MVP
Frequent Visitor

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:

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors