Group measures by column field in matrix

[FrancescoSCP]

Hi guys,

i need to group measures in a matrix by a specific field.

Let's make an example. This is what i want:

But with Power BI desktop the result is:

Is there a way to group measures like in the first image?

Thanks in advance.

Francesco

[Dimitris_Kats]

Hi. That was an excellent solution. Is there any way to achieve the same results without switch or if?? 

[vishalgour5886]

Hi,

First of all, thank you very much for your assistance in resolving my problem. Going forward, I have an additional step to address. Please assist.

When I try to group the measures with columns in power bi, I get an error, so I created a new table with records of measures in columns and created a calculated column for grouping. Please review the steps below.

Step 1: Choose the enter data option and group the column into the calculated measures

Step 2: Add a column for the measure name, as well as a column for the index number, to the custom table.

Step 3: When the table is created then we will group the Metric column into sub column

-> Use the new group option

 
Step 4: Add the subcategory name to the category name

Step 5: Create a group table with metric type column and metric subcolumn

Step 6: Adding the column corresponding to the group table and entering the measure shown in the image

Step 7: As a final step, we grouped the calculated measures (Month 1 & Month 2) by column and created a matrix

-> Here is a code for your reference

Month 1 = IF('Table'[Metric Type]="Book Size and Growth" && 'Table'[Metric]="AUM (Rs Cr)", 'Key Measure'[AUM (Rs Cr)_],

IF('Table'[Metric Type]="Book Size and Growth" && 'Table'[Metric]="Live Accounts", [Live Accounts Latest Month],

IF('Table'[Metric Type]="Book Size and Growth" && 'Table'[Metric]="Cumulative Disbursement (Rs Cr)", [Cumulative Disbursement (Rs Cr) Latest Month],

IF('Table'[Metric Type]="Book Size and Growth" && 'Table'[Metric]="Average Ticket Size (Rs Lacs)", [Average Ticket Size (Rs Lacs)_],

IF('Table'[Metric Type]="Entry Rates" && 'Table'[Metric]="% Bounce Rate (Bucket X)", [% Bounce Rate (Bucket X) Latest Month],

IF('Table'[Metric Type]="Entry Rates" && 'Table'[Metric]="Total Bounce Rate", [Total Bounce Rate],

IF('Table'[Metric Type]="Entry Rates" && 'Table'[Metric]="X+ Amt% excl.  w/o", [X+ Amt% excl.  w/o],

IF('Table'[Metric Type]="Portfolio Performance" && 'Table'[Metric]="30+ Amt% excl. w/o", [30+ Amt% excl. w/o Latest Month],

IF('Table'[Metric Type]="Portfolio Performance" && 'Table'[Metric]="90+ Amt% excl. w/o", [90+ Amt% excl. w/o Latest Month],

IF('Table'[Metric Type]="Portfolio Performance" && 'Table'[Metric]="Current Year w/o (Rs Cr)", [Current Year w/o (Rs Cr)],

IF('Table'[Metric Type]="Portfolio Performance" && 'Table'[Metric]="Current Year w/o  (% wrt Mar NEA)", [Current Year w/o  (% wrt Mar NEA)],

IF('Table'[Metric Type]="Portfolio Performance" && 'Table'[Metric]="Cumulative w/o stock (Rs Cr)", [Cumulative w/o stock (Rs Cr) Latest Month],

IF('Table'[Metric Type]="Portfolio Performance" && 'Table'[Metric]="Cumulative w/o stock % (% wrt till last Mar cumulative Disb)", [Cumulative w/o stock % (% wrt till last Mar cumulative Disb)],

IF('Table'[Metric Type]="Portfolio Performance" && 'Table'[Metric]="COVID-19 restructuring (Rs Cr)", [COVID-19 restructuring (Rs Cr) Latest Month],

IF('Table'[Metric Type]="Portfolio Performance" && 'Table'[Metric]="COVID-19 restructuring (%)", [COVID-19 restructuring (%) Latest Month],

IF('Table'[Metric Type]="Portfolio Performance" && 'Table'[Metric]="Stress Portfolio (% NEA)", [Stress Portfolio (% NEA) Latest Month]

))))))))))))))))

[PavitraTalluri]

Can this matrix visual interact with Slicers in PBI?

[amitchandak]

check this

https://community.powerbi.com/t5/Desktop/Matrix-Display-Values-above-Columns/td-p/256905

[FrancescoSCP]

Hi, thanks for this.

But what are the contraindications for unpivot columns? I use this two measures also in other tables and graphs.

Thanks,

Francesco

[FrancescoSCP]

Hi,

i've tried this solution but i have the measure always grouped.

This mean that i can not use either SALES or ORDERS alone in one visualization. Right?

EDIT: i can duplicate the measures column and then do the unpivot. But on the other hand it multiplies the number of record of my tables.

I think i have to wait for an improvement of the matrix visualization.

Thanks,

Francesco

[Vamshi_R]

Hi FrancescoSCP,

Please follow below steps and I worked out only for your example data, you may use my steps as an idea for production data.

1. Create a data table as shown below,

2. Create Measures for years or (If you have any other measures) like shown in picture below, and use matrix visual with "Type" from data table as Column. 

Measure I used for Ex.    

---

2018 =

VAR mystatus = SELECTEDVALUE('Type'[Type])

Return

SWITCH(TRUE(),mystatus="Sales",CALCULATE(SUM(Data[Sales]),YEAR(Data[year])=2018),

mystatus="Orders",CALCULATE(SUM(Data[orders]),YEAR(Data[year])=2018),""

)

----

Let me know if that helps my friend.

Vamshi

[sirlanceohlott]

@Vamshi_R excellent solution, I was actually in search of something like this and it worked wonderfully!

[Vamshi_R]

That's cool my friend... 🤝

[Analitika]

@Vamshi_R 
Very nice example, but what if 2017 is empty for ORDERS? How to not show 2017 column at all in Orders instead of showing empty column?

[Vamshi_R]

Thank you.. You may try using Filters pane to restrict 'No blanks' or not value 0.. hope that may work.