Classify values into 3 groups

[joshua1990]

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?

[v-yangliu-msft · Accepted solution]

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