Pie Chart with percent data on multiple columns

[Anonymous]

Hi there,

I have an excel file that has the following fields:

Type 1         |        Type 2      |     Type 3

Sun                    Sun                   Moon

Moon                Planet                 Sun

I need to have a Pie Chart that displays the total split % or volume such as :

Sun - 3

Moon - 2

Planet - 1

The problem is that I cannot put all of their legends, so I will get a combine value but rolling up only to the Types existent in Type 1 ( or Sun | Moon).

How can I create a pie chart that shows all of the data labels with their combined values?

Thanks

[amitchandak]

@Anonymous , use unpivot or transpose and then you can count

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

[Anonymous]

If I use those, it breaks the rest of the table and formulas that I have. Are there any other ways?

[v-yingjl]

Hi @Anonymous ,

If you do not want to break the initial table, you can use union() function to create a new calculate table:

new table =
UNION (
    SUMMARIZE ( 'Table', 'Table'[Type1] ),
    SUMMARIZE ( 'Table', 'Table'[Type2] ),
    SUMMARIZE ( 'Table', 'Table'[Type3] )
)

Rename the column as 'Type', create a pie chart to get the result:

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yingjl,

Thank you! That did work partly, but I still have a couple of issues where I would love to get your insights:

1. By creating a separate table, I lost the ability to filter my data dynamically. Is there a way I can add another column onto this table "ID" and then create a relationship between both tables so I don't lose the filtering functionality?
2. Although it grouped the items of some of the tables, I only got 28 in the pie chart when the real count is 46. Any ideas on what could be trigering this?
   
   Thanks

[v-yingjl]

Hi @Anonymous ,

Could you please consider sharing a sample .pbix file for further discussion? Remember delete or replace sensitive information when you share it.

Best Regards,

Yingjie Li