Forum Discussion

Chrissie's avatar
Chrissie
Frequent Visitor
4 years ago
Solved

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...
Need Help
  • speedramps's avatar
    speedramps
    4 years ago

    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()
    )
speedramps's avatar
speedramps
Super User
4 years ago

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()
)
  • Chrissie's avatar
    Chrissie
    Frequent Visitor
    4 years ago

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

    • speedramps's avatar
      speedramps
      Super User
      4 years ago

      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.