Forum Discussion
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.
Hi clim2f88j
To achieve your first goal please follow next steps:
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
Hi clim2f88j you can use "calculate" for this purpose.
But that means, you need to create separate measures for every manufacturer.
Please Follow this step if that is your desire:
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.
Here is a linkIf this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
9 Replies
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))RETURNDIVIDE(A, B)If this helps, mark it as solution to help others. You can appreciate with a Kudo!
Regards,
Hi clim2f88j
To achieve your first goal please follow next steps:
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
Hi Thejeswar
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
