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 ?

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

CodePudding user response：

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