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

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])ReturnSWITCH(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

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

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

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

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

Group measures by column field in matrix | Microsoft Fabric Community

11 Replies

amitchandak
Super User
7 years ago
check this
https://community.powerbi.com/t5/Desktop/Matrix-Display-Values-above-Columns/td-p/256905
- FrancescoSCP
  Helper IV
  7 years ago
  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
    Helper IV
    7 years ago
    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
vishalgour5886
New Member
4 years ago
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]
))))))))))))))))
- Anonymous
  Not applicable
  3 years ago
  Can this matrix visual interact with Slicers in PBI?
Dimitris_Kats
Helper V
2 years ago
Hi. That was an excellent solution. Is there any way to achieve the same results without switch or if??