Pie Chart counting data from different columns

[Anonymous]

Hello everyone,

I am quite new to Power BI,  will try to explain you my problem as best as I can:

I have a table like this:

Company name    Country              Service 1              Service 2                 Service 3

A                           US                      HR                        Payroll                     CAS

B                           Germany            Payroll                  Setup                              

C                           China                  Setup                   CAS                          IT

I am basically trying to record all my clients together with country of origin and which service they requested. Please note that since a single client usually requires more than one service I had to create multiple columns.

Now, I want to create a pie chart that shows which services customers requested, counting them across the three columns. 

So I want a pie that says Payroll 2 Setup 2 HR 1 and so on, without mentioning which column they come from. 

I cannot unpivot the columns because I have a map that shows whih countries the clients are from and I want the map to change according to the service I click on, like I click on CAS and I want to see US and China on the map, or vice versa. 

It would be great if you could help me step by step since I am still quite not familiar with the commands. 

Thank you so much

Matteo 

[V-lianl-msft]

Hi @Anonymous ,

Assume your table is like:

You can copy the table, and then "unpivot" the copied table. Then in Edit quieries, select all subject columns from "Service1" to "Service3", then in "Transform"->"Unpivot columns", the dataset would change as below:

Then the pie chart can count the number of services:

Best Regards,

Liang

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

[Anonymous]

Thank you Liang for your response.
 
I realize I have made the table too simplistic, but I actually have way more columns and about 2000 rows. I am also using functions such as count the number of elements in my dashboard, so as I was mentioning pivoting is not an option for me.
 
Do you think there is any way to solve the problem, for example, using the new measure function directly in the report view? Or through a simple piece of coding?

Thank you in advance for your help

Matteo