Howto make one Power BI pie chart widget from two columns data / or merge two pies in one

user_14662179 0
Viewed 488

I have a table with two columns (more in real life)

| firmware_check_result    | software_check_result |
| -------------------------|-----------------------|
| firmware checksum error  | software outated      |
| firmware outated         | software slow         |
|                          | software bug          |

Results can be any text from a predefined list of reasons. But results are different in both columns. And a row can have one or no firmware check result and one or no software check result.

I need to build one unique pie chart with all the data from both columns. Is it possible ? and how to to it ?

I tried to add both columns like this:

  • Drop a Pie chart on canvas
  • in Properties:
    • in Legend: I added both columns
    • in Values: I added both columns

Result is strange as I have one pie chart but legend is only the legend of the 1st column, not the second.

It there a way to do this ?

enter image description here This shows a result with 3 results possibilities but legend has only two and colors are the same for 2 out of 3 results.

pie-chart powerbi user-interface
1 Answers

Here is one approach. For simplicity I will use only your example columns in my answer:

  1. Create a new query using the same data as before

    • Keep only the firmware_check_result and software_check_result columns, as well as an index or unique identifier (UID) column of some kind (and any other information you think may be useful later)
      • I will refer to the UID column as [UID]

  2. Select and unpivot your firmware_check_result and software_check_result columns in the new query

    • Close and apply the changes

  3. Create a new relationship between your original table and your new unpivoted table based on [UID]

  4. Create a new pie chart

    • For your values, use Count of [UID]
    • In the legend, from your new unpivoted query, place Attribute first, then Value underneath
      • You can switch between the two levels using Drill Up and Expand all down one level in hierarchy or Go to next level in hierarchy
user_170997420
Related