cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Post Prodigy

## Classify values into 3 groups

Hi experts!

I have a table that contains different values for each article for each week:

 Date Amount Price Article Value 1 Value 2 Value 3 01.01.2024 500 4 A 1 2 3 01.02.2024 600 5 B 2 1 2

Now I would like to transpose these values into a matrix on the rows and then make them visible for each individual classification. These classifications are like:

• Class 1: Value 1 below 2
• Class 2: Value 2 below 2
• Class 3: Value 3 and Value 1 above 0

In the end I would like to display this in the following way:

 Article Date Measure Class 1 Class 2 Class 3 A 01.01.2024 Amount 500 500 A 01.01.2024 Price 4 4 B 01.02.2024 Amount 600 600 B 01.02.2024 Price 5 5

As you can see, the values for each measure get's displayed when the classification is true or false.

How would you do this?

1 ACCEPTED SOLUTION
Community Support

Hi  @joshua1990 ,

Here are the steps you can follow：

1. In power query – select [Amount] and []Price] – Transform – Unpivot Columns.

2. In power query – select [Value1], [Value2], [Value3]– Transform – Unpivot Columns.

3. Create measure.

``````Measure =
SWITCH(
TRUE(),
OR(
MAX('Table'[Article])="B"&&
MAX('Table'[Attribute.1])="Value 1"&&MAX('Table'[Attribute])="Amount",
MAX('Table'[Article])="B"&&
MAX('Table'[Attribute.1])="Value 1"&&MAX('Table'[Attribute])="Price"),
BLANK(),
OR(
MAX('Table'[Article])="A"&&
MAX('Table'[Attribute.1])="Value 2"&&MAX('Table'[Attribute])="Amount",
MAX('Table'[Article])="A"&&
MAX('Table'[Attribute.1])="Value 2"&&MAX('Table'[Attribute])="Price"),
BLANK(),
MAX('Table'[Value]))
``````

4. Matrix Visual – Fromat – Row headers – Options – stepped layout – set off.

5. Result:

Best Regards,

Liu Yang

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

Community Support

Hi  @joshua1990 ,

Here are the steps you can follow：

1. In power query – select [Amount] and []Price] – Transform – Unpivot Columns.

2. In power query – select [Value1], [Value2], [Value3]– Transform – Unpivot Columns.

3. Create measure.

``````Measure =
SWITCH(
TRUE(),
OR(
MAX('Table'[Article])="B"&&
MAX('Table'[Attribute.1])="Value 1"&&MAX('Table'[Attribute])="Amount",
MAX('Table'[Article])="B"&&
MAX('Table'[Attribute.1])="Value 1"&&MAX('Table'[Attribute])="Price"),
BLANK(),
OR(
MAX('Table'[Article])="A"&&
MAX('Table'[Attribute.1])="Value 2"&&MAX('Table'[Attribute])="Amount",
MAX('Table'[Article])="A"&&
MAX('Table'[Attribute.1])="Value 2"&&MAX('Table'[Attribute])="Price"),
BLANK(),
MAX('Table'[Value]))
``````

4. Matrix Visual – Fromat – Row headers – Options – stepped layout – set off.

5. Result:

Best Regards,

Liu Yang

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors