Forum Discussion

Gazanfar's avatar
Gazanfar
Frequent Visitor
4 years ago
Solved

DAX COMMAND

I have data in excel as shown below.

 

     C1_1                C1_2                 C1_3              C1_4               C1_5

AffordableAffordableDo not knowDo not knowAffordable
AffordableAffordableDo not knowDo not knowDo not know
AffordableAffordableDo not knowAffordableAffordable
AffordableAffordableDo not knowDo not knowAffordable
Not affordableNot affordableDo not knowDo not knowNot affordable
AffordableDo not knowAffordableAffordableAffordable
AffordableDo not knowDo not knowDo not knowAffordable
AffordableDo not knowNot affordableDo not knowDo not know
AffordableAffordableDo not knowDo not knowDo not know
AffordableDo not knowDo not knowDo not knowDo not know
AffordableAffordableDo not knowDo not knowDo not know
AffordableAffordableDo not knowDo not knowDo not know

 

And I want to get visualization in Power BI as shown below. I do not know which measures I have to use to get this kind of table in Power BI. If anyone knows please help. Thanks in advance!

 

 

  • Syk's avatar
    Syk
    4 years ago

    Once you load your data, click "transform data" to bring up your power query window.
    Once that window opens, select all of your columns and then under the transform tab, click unpivot columns
    This should give you a table with 2 columns - Attribute and Value (see screenshot using your example data)

    Close and apply these changes.
    Add a matrix visualization to your canvas, add the Attribute field to Rows and Value to columns
    Then add your Attribute field to the values. (It should change to "First Attribute" because it is a text field) click the arrow next to it and select "Count" instead of "First".
    Then select the same arrow and under "Show value as" select "Percent of row total" for your use case. Then remove the totals from the matrix and you should have what you need!

     

     

3 Replies

  • Syk's avatar
    Syk
    Resident Rockstar
    4 years ago

    Once you load your data, click "transform data" to bring up your power query window.
    Once that window opens, select all of your columns and then under the transform tab, click unpivot columns
    This should give you a table with 2 columns - Attribute and Value (see screenshot using your example data)

    Close and apply these changes.
    Add a matrix visualization to your canvas, add the Attribute field to Rows and Value to columns
    Then add your Attribute field to the values. (It should change to "First Attribute" because it is a text field) click the arrow next to it and select "Count" instead of "First".
    Then select the same arrow and under "Show value as" select "Percent of row total" for your use case. Then remove the totals from the matrix and you should have what you need!

     

     

    • Gazanfar's avatar
      Gazanfar
      Frequent Visitor
      4 years ago

      Thank you Syk, you explained everything in detail and your advice helped me too much! 

      • Syk's avatar
        Syk
        Resident Rockstar
        4 years ago

        Great to hear! Consider marking that reply as the solution so others can easily find it if they have a similar issue!