Forum Discussion
Reporting on delimiter on single chart
Hi everyone,
I am working on a project in Power BI where I am taking an excel document where people picked their favorite applications in office but it could have been multiple choice. An example would be
| Name | Favorite Part of Office |
| John | Outlook;Word |
| Jane | Word |
What is the best way to report on their favorite part of Office under one graph? Currently in PowerBI it will make "Outlook;Word" one selection so depending on the order they checked the answers it gets really messy really fast.
Hi TRISTANCARY
I created a sample ,maybe you can refer to it .
Original Date:
(1)Use Split Column in Query Editor , to divide a column of values into multiple columns of single values according to the separator, like this
(2)Return to desktop view ,and create measures to count the rows for each part .
Word = COUNTROWS(FILTER('Table','Table'[Favorite Part of Office.1]="Word" || 'Table'[Favorite Part of Office.2]="Word" || 'Table'[Favorite Part of Office.3]="Word"))Outlook = COUNTROWS(FILTER('Table','Table'[Favorite Part of Office.1]="Outlook" || 'Table'[Favorite Part of Office.2]="Outlook" || 'Table'[Favorite Part of Office.3]="Outlook"))Excel = COUNTROWS(FILTER('Table','Table'[Favorite Part of Office.1]="Excel" || 'Table'[Favorite Part of Office.2]="Excel" || 'Table'[Favorite Part of Office.3]="Excel"))The results are as shown:
(3)Add a pie chart ,in the visual ,put the measures in Values .
I have attached my pbix file ,you can have a look .
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- parry2k
Super User
TRISTANCARY it will make sense to split the data into rows in PQ and then everything will be easy to visualize
Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
If you split it into rows can you add all split rows into the same graph? I don't believe power bi let's you combine multiple rows into one graph
When combining values into a single graph like below:
It seems to take the values and even with the same information it keeps it as seperate precentages from their different columns. Is there a way to unify them?
- parry2k
yes, you can.
Hi TRISTANCARY
I created a sample ,maybe you can refer to it .
Original Date:
(1)Use Split Column in Query Editor , to divide a column of values into multiple columns of single values according to the separator, like this
(2)Return to desktop view ,and create measures to count the rows for each part .
Word = COUNTROWS(FILTER('Table','Table'[Favorite Part of Office.1]="Word" || 'Table'[Favorite Part of Office.2]="Word" || 'Table'[Favorite Part of Office.3]="Word"))Outlook = COUNTROWS(FILTER('Table','Table'[Favorite Part of Office.1]="Outlook" || 'Table'[Favorite Part of Office.2]="Outlook" || 'Table'[Favorite Part of Office.3]="Outlook"))Excel = COUNTROWS(FILTER('Table','Table'[Favorite Part of Office.1]="Excel" || 'Table'[Favorite Part of Office.2]="Excel" || 'Table'[Favorite Part of Office.3]="Excel"))The results are as shown:
(3)Add a pie chart ,in the visual ,put the measures in Values .
I have attached my pbix file ,you can have a look .
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
