Forum Discussion

Saxon202202's avatar
Saxon202202
Helper III
3 years ago
Solved

Matrix table using multiple column

Hi,

I have data data table and the table contain country id, country code and qty.

 

In visualisation, I like to breakdown by country id, country code and qty in matrix table.
At the moment all the country code showing in one column by country id but the expected result is one by one, row by column (country Id by country code). Is that possible to achieve in visuslation?

Example: Return the releevant country code in each columns. If country code - RS in row and column will be show only RS country code.

 

 

 

 

 

https://www.dropbox.com/s/18q4wdjaszlml3x/PBI%20VISUAL%20-MATRIX.pbix?dl=0

Need Help
  • danextian's avatar
    danextian
    3 years ago

    Hi Saxon202202 

    I've edited the PBIX with a calculated column to rank Country Code by Country ID.

    Index = 
RANKX (
    FILTER ( DATA, DATA[COUNTRY ID] = EARLIER ( DATA[COUNTRY ID] ) ),
    DATA[COUNTRY CODE],
    ,
    ASC,
    DENSE
)

10 Replies

  • danextian's avatar
    danextian
    Super User
    3 years ago

    Hi Saxon202202,

     

    If I got your user case right, my approach would be to create an index column for each Country ID, use that as the column header and use SELECTEDVALUE to show the country code in the row. This is the closest I could get to. Please refer to the attached PBIX

     

     

     

    • Saxon202202's avatar
      Saxon202202
      Helper III
      3 years ago

      Hello, Thanks for your suggestion. The country id is not part of raw data, it's came from DAX code based on the different columns so unable to modify the changes in Power query. Is that any chance can I have the same index output by using DAX code. I can get the same output in Excel by using COUNTIFS =COUNTIFS(A2:A$2,A2) but not sure how can make DAX code to get the same output. 

      • danextian's avatar
        danextian
        Super User
        3 years ago

        Hi Saxon202202 

        I've edited the PBIX with a calculated column to rank Country Code by Country ID.

        Index = 
RANKX (
    FILTER ( DATA, DATA[COUNTRY ID] = EARLIER ( DATA[COUNTRY ID] ) ),
    DATA[COUNTRY CODE],
    ,
    ASC,
    DENSE
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  ,

    This can be resolved using switch function

    SWITCH = 

SWITCH(
    TRUE(),
    SELECTEDVALUE(DATA[COUNTRY ID]) = "RS",SELECTEDVALUE(DATA[COUNTRY CODE]),
    SELECTEDVALUE(DATA[COUNTRY ID]) = "PD",SELECTEDVALUE(DATA[COUNTRY CODE]),
    SELECTEDVALUE(DATA[COUNTRY ID]) = "OQ",SUM(DATA[ORDER QTY])
)



     

    BR

    Saxon202202

    • Saxon202202's avatar
      Saxon202202
      Helper III
      3 years ago

      Anonymous @, Thanks for your reply. The country id should be show the relevant country code.