cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Pie Chart Separating Values in Same Row

This is the data columns i have in Power BI:

 Client ID Manufacturer 223 Honda 345 Kia 777 VW, Seat 696 Seat 122 Honda, Kia, VW 009 Kia, Seat 845 VW 123 Honda, Seat 321 Kia, Seat

As you can see, there are some "Manufacturers" that have multiple values in them.

I need to create a pie chart that shows the total manufacturers selected individually. Meaning that if Cliend ID: "122" has Honda, Kia, and VW then thats a selection for each one and should show individually in the pie chart. If i create a pie chart right now with the data i have then the key would show "Honda, Kia, VW" as one when it should be separated.

Final outcome i would like:

Key:

 Manufacturer Count Honda 3 Kia 4 VW 3 Seat 5

The next step after getting the above, or maybe should do this from the start, would be for the key to show the overall percentage instead of the individual count. So in this case if the total count is 15 then i need to divide each count by 15, but i would need to have a calculation to sum all the manufacturers.

1 ACCEPTED SOLUTION
Super User

Hi @clim2f88j  you can use "calculate" for this purpose.
But that means, you need to create separate measures for every manufacturer.
1. Add Column to count words of your manufacturers : ( you will need it for your "ALL")

2. Than count percent of every manufacturer by formula:

Percent KIA =
Divide(CALCULATE (
COUNTX ( 'Table whithout splitting',[Client ID] ),
FILTER (
'Table whithout splitting',
CONTAINSSTRING ( 'Table whithout splitting'[Manufacturer], "Kia" ) = TRUE
)
) ,
CALCULATE(sum('Table whithout splitting'[Words Column Counter]),all('Table'[Client ID])))

Then you need to put these measures separately.
I have updated the Sample file again.

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

9 REPLIES 9
Super User

Hi @clim2f88j
1. In PQ you need to extract your rows with  values to different rows as split by comma as shown at the pictures :

2. You need to Trim the column of manufacturer :

3. Just "close and apply" and put it on your pie chart

****

In terms of data visualization, this type of graph is ineffective and even incorrect for two reasons.

In the first place, our brain is unable to calculate the areas of segments

A second and more critical point is that you don't show one part of the whole here. Instead, you rank the parts based on their dominance (percent of all).

Therefore, I recommend switching to a bar chart, as shown in the image.

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

Resident Rockstar

Your solution is not complete, You are showing duplicates for manufacturers. That is why your Pie Chart is too clumpsy

Super User

I appreciate you bringing to my attention the lack of "TRIM".

Corrections have been made to the solution and sample file.

. It does not change the fact that a pie chart is incorrect in the above example.
In data visualization, there is no such thing as "is too clumsy".
Pie charts show a part of the whole.
It was right if the question was which part of the whole is the seat.
The pie becomes an effective graph when there is a comparison of several parts.
Since ranking is a vertical comparison made by our brain, and a percent is just a unit of comparison

Frequent Visitor

Haven't tried it yet but this seems easy to follow.

Follow up question: My data is connected to SharePoint, therefore each time i refresh it its updated or new data added. Are there any issues with adding columns in PQ like this? Will it affect the excisting data when i refresh? Will the new incoming data be split as well?

Thank you!

Super User

A POWER QUERY's steps are recorded in the file's memory (similar to macro recordings and other automations).

Refreshing the report causes them to be redone.

As a result, any new rows that are pulled from your data source will be processed exactly the same way as the existing rows.

I hope this information was helpful

Rita:)

Frequent Visitor

So the issue i ran into by splitting the column by delimeter is that now i will have "duplicate" records. This will affect all my other pages and graphs i have since it will think i have more results in the PQ that it should.

Is there a way in the chart to do a DAX, similar as an Excel formula where if "countif" contains *specific manufacturer* then provide that total?

Super User

Hi @clim2f88j  you can use "calculate" for this purpose.
But that means, you need to create separate measures for every manufacturer.
1. Add Column to count words of your manufacturers : ( you will need it for your "ALL")

2. Than count percent of every manufacturer by formula:

Percent KIA =
Divide(CALCULATE (
COUNTX ( 'Table whithout splitting',[Client ID] ),
FILTER (
'Table whithout splitting',
CONTAINSSTRING ( 'Table whithout splitting'[Manufacturer], "Kia" ) = TRUE
)
) ,
CALCULATE(sum('Table whithout splitting'[Words Column Counter]),all('Table'[Client ID])))

Then you need to put these measures separately.
I have updated the Sample file again.

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

Frequent Visitor

So what i did instead is, i duplicated the main query and split my column there. Then i used the query with the split column to create the chart. It's working fine, so let me know if you see an issue with doing this.

Resident Rockstar

Hi @clim2f88j ,

1. Select the Manufacturer Column and Use Split by Delimter to split the entries ("Comma" delimiter to be use with each occurence"

2. Post which the data would look like this

3. Now, select the 3 manufacturer columns and click on Unpivot Columns as shown below

4. After doing step 3, this is how the table will look

5. Remove the Attribute column and rename Value to Manufacturer. PFB the screenshot for the same

6. Select the Manufacturer Column and Trim it using the Format Drop Down to remove any unwanted spaces in the names. This will help iwth grouping the column for getting the count and percentage

7. Get the Count of Manufacturer using the below measure

No of Manufacturers = COUNT(Manufacturer_Table[Client ID])
8. Get the Percentage using the below measure
% of manufacturers =
VAR A = COUNT(Manufacturer_Table[Client ID])
VAR B = CALCULATE(COUNT(Manufacturer_Table[Client ID]), ALL(Manufacturer_Table))
RETURN
DIVIDE(A, B)

If this helps, mark it as solution to help others. You can appreciate with a Kudo!

Regards,

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors