Add a second header in matrix PBI with no hierarchy defined

[Anonymous]

Hi all,

I have the following table:

And I need to build a matrix in PBI as the following, but withouth unpivot columns:

Does anyone know how to do it?

Thanks a lot!

[MFelix]

Hi @Anonymous,

For doing this you have currently two options.

Field parameters

In this case  you can create a field parameter table (see link) then on this table add a column with the category you need to group by and you can then use that on your matrix.

Disconnected table

This is similar to the other option however you need to build the table manually so you would get something similar to this:

Group	Measure	ID
Total Sales	Total_Sales_Wheels	1
Total Sales	Total_Sales_Engines	2
Total Costs	Total_Costs_Wheels	3
Total Costs	Total_Costs_Engines	4

Now you just need to create a metric similar to the one below:

Matrix value =
SWITCH (
    SELECTEDVALUE ( Table[ID] ),
    1, [Total_Sales_Wheels],
    2, [Total_Sales_Engines],
    3, [Total_Costs_Wheels],
    4, [Total_Costs_Engines]
)

Now use this metric on the matrix visualization.

[JoaoMarcelino]

Amazing solution, I also love to use Fields parameter 🙂

[v-henryk-mstf · Accepted solution]

Hi @Anonymous ,

If the second header statistics is similar to the aggregated value, you can try multiple measures to achieve a similar effect.

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[MFelix]

@v-henryk-mstf ,

Not sure why this is accept has an answers this is the oposite of what the user want he want to group the measure by type (sales, cost) and then by product.

Does not make sense to have this answer selected.