Classify values into 3 groups

joshua1990 — Fri, 01 Sep 2023 05:41:06 GMT

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?

Re: Classify values into 3 groups

Anonymous — Mon, 04 Sep 2023 02:00:15 GMT

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

topic Classify values into 3 groups in DAX Commands and Tips

Classify values into 3 groups

Re: Classify values into 3 groups