Create Category Groups based on Column Names of the table

[SwissCake]

Hi All

I am still learning power BI and was wondering if it is possible to create groups to use in the matrix visual based on column names of the table.

My underlying table (Date Source) looks something like this. There is one entry per date and values for each "category". There are no foreign keys, just a date column and values for each "category column".

The Total credit risk is the sum of the other three columns. I would like to see the values grouped by category something like this below in order to use in a matrix visual (just with the values next to it the category names from above. I tried to use google but did not find something useful/which I understood)

Would be really appreciated if someone could help me out. Is there any way of doing that/ to create a suitable lookup table to create a one to many relationship or so/ or any other way?

Best Regards

SwissCake

[v-yueyunzh-msft · Accepted solution]

Hi, @SwissCake 

According to your description, you want to show the [Total Credit Risk] as a hierarchy.

Here are the steps you can refer to :
(1)This is my test data:

(2)We need to create a dimension table like this:

(3)Then we can create two measures:

Measure = var _cur_column = MAX('Row'[Column1])
return
SWITCH( _cur_column , 
"Positions at 20%", SUM('Table'[Positions at 20%]),
"Positions at 3%", SUM('Table'[Positions at 3%]),
"Top-up",SUM('Table'[Top-up]))

Measure 2 = SUMX( CROSSJOIN(VALUES('Table'[Date]) , VALUES('Row'[Column1]) ) , [Measure])

(4)Then we can put the field we need on the matrix visual and configure the options in Matix we need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[SwissCake]

Wow, just incredible😊

The speed and the precision in which my question was answered are outstanding! Thank you so much, it works perfectly and now I know how to solve some similar problems. 

As I am still learning DAX, I did not know the Switch/Corssjoin function! Definetly looking forward to my advanced DAX course where this is hopefully covered!

Best Regards

SwissCake

[Greg_Deckler]

@SwissCake You could do this as a calculated column with a SWITCH(TRUE(),...) statement.

[tmhalila]

Hi @Greg_Deckler I just tagged you in another thread because this is already closed so you can assist in providing your idea of using SWITCH(TRUE(),...) in a similar form of scenario.

This will be much appreciated! Thanks