Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Chrissie
Frequent Visitor

Stacking Measures in Matrix table like the structure in Excel

Hi Guys,

 

We used to build matrix report in below structure in Excel or other reporting platform. However, when it comes to using Measures in Power BI, I have not found a way to stack them in such structure yet.

 

Chrissie_0-1650726532457.png

Below is a workaround structure in Power BI. But, as many Power BI folks are asking the same question. I wanted to throw this question out there to see if anyone else has a solution. How can we stack measures in Power BI as presented above?

 

Chrissie_1-1650726591802.png

 

Looking forward to your solutions!

1 ACCEPTED SOLUTION
speedramps
Super User
Super User

Hi Chrissie

 

Please consider this solution.
I am an unpaid Power BI volunteer so please click the thumbs up if you like me trying to help you and the solved button if I fix your problem. 

 

Create the followind 2 dynamic measures. 

Then create a matrix with rows = Zone and Type,
leave Columns = blank 

and Values = Score 1 and Score 2

Score 1 =
VAR mytype = SELECTEDVALUE(Facts[Type])
RETURN
SWITCH(TRUE(),
mytype = "Type 1", [Measure1],
mytype = "Type 2", [Measure2],
BLANK()
)
 
Score 2 =
VAR mytype = SELECTEDVALUE(Facts[Type])
RETURN
SWITCH(TRUE(),
mytype = "Type 1", [Measure3],
mytype = "Type 2", [Measure4],
BLANK()
)

View solution in original post

3 REPLIES 3
speedramps
Super User
Super User

Hi Chrissie

 

Please consider this solution.
I am an unpaid Power BI volunteer so please click the thumbs up if you like me trying to help you and the solved button if I fix your problem. 

 

Create the followind 2 dynamic measures. 

Then create a matrix with rows = Zone and Type,
leave Columns = blank 

and Values = Score 1 and Score 2

Score 1 =
VAR mytype = SELECTEDVALUE(Facts[Type])
RETURN
SWITCH(TRUE(),
mytype = "Type 1", [Measure1],
mytype = "Type 2", [Measure2],
BLANK()
)
 
Score 2 =
VAR mytype = SELECTEDVALUE(Facts[Type])
RETURN
SWITCH(TRUE(),
mytype = "Type 1", [Measure3],
mytype = "Type 2", [Measure4],
BLANK()
)

It looks like a possible solution. I will test to see if it works once I get a chance. Thank you very much!

I am an unpaid power Bi volunteer. Please click solved to accept the solution so we get kudos. If your problem has expanded or changed then set this one to solved and raise a new ticket, because this solution does seem to answer the original question. Many thanks.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.