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 structure yet.

 

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?

 

 

Looking forward to your solutions!

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

3 Replies

  • 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.