Forum Discussion
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?
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
1 Reply
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