cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FrancescoSCP
Helper III
Helper III

Group measures by column field in matrix

Hi guys,

 

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

 

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

 

But with Power BI desktop the result is:

pivot2.png

 

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

 

Thanks in advance.

 

Francesco

9 REPLIES 9
vishalgour5886
New Member

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

vishalgour5886_0-1658215237569.png

 

 

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

 

vishalgour5886_1-1658215305044.png

 



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

 

vishalgour5886_2-1658215350470.png

 



-> Use the new group option

vishalgour5886_3-1658215377704.png

 


Step 4: Add the subcategory name to the category name

 

vishalgour5886_4-1658215406459.png

 



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

 

vishalgour5886_5-1658215434305.png

 



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

 

vishalgour5886_6-1658215465770.png

 



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

 

vishalgour5886_7-1658215500756.png

 



-> 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]

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

amitchandak
Super User
Super User

check this

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



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

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

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,

 

Data table.PNG

 

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),""
)
----

 

Powerbi Screen.PNG

 

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!

That's cool my friend... 🤝

@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?

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

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors