Skip to main content
cancel
Showing results for 
Search instead 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

Reply
clim2f88j
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:

clim2f88j_1-1684627888969.png

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. 

 

2 ACCEPTED SOLUTIONS
Ritaf1983
Super User
Super User

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 :

Ritaf1983_0-1684638450858.pngRitaf1983_1-1684638536306.png

 

2. You need to Trim the column of manufacturer :

Ritaf1983_0-1684640158949.png

 

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

Ritaf1983_1-1684640233638.png

****

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.

Ritaf1983_3-1684641416414.png

 

 

Link to sample file 

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



Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

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")

Ritaf1983_0-1684807035821.png

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])))

 

Ritaf1983_1-1684807626034.png

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

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

9 REPLIES 9
Ritaf1983
Super User
Super User

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 :

Ritaf1983_0-1684638450858.pngRitaf1983_1-1684638536306.png

 

2. You need to Trim the column of manufacturer :

Ritaf1983_0-1684640158949.png

 

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

Ritaf1983_1-1684640233638.png

****

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.

Ritaf1983_3-1684641416414.png

 

 

Link to sample file 

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



Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

@Ritaf1983 ,

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

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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!

Hi @clim2f88j 

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:)

 

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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?

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")

Ritaf1983_0-1684807035821.png

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])))

 

Ritaf1983_1-1684807626034.png

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

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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.

Thejeswar
Community Champion
Community Champion

Hi @clim2f88j ,

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

Thejeswar_1-1684637708920.png

 

2. Post which the data would look like this

Thejeswar_0-1684637664067.png

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

Thejeswar_2-1684637790509.png

 

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

Thejeswar_3-1684637834648.png

 

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

Thejeswar_4-1684637881050.png

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

Thejeswar_5-1684638066073.png

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)
 
Thejeswar_7-1684638429179.png

 

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

 

Regards,


 

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.