Forum Discussion
Organising cells with multiple data values
Hello
I have recently started using Power Bi to summarise data on our partners. Below is a basic version how the data is extracted:
Name Products Business locations
| Customer | Products | Locations |
| Customer 1 | Product A; Product B; Product C; Product D | Residential; Retail; School; Hospital |
| Customer 2 | Product A; Product D | Residential; Retail; Hospital |
| Customer 3 | Product B; Product C | School; Hospital |
| Customer 4 | Product A; Product B; Product D | Residential; Retail; School; |
In the report I would like to show:
- How many customers sell Product A/Product B/Product C/Product D
- How many customers sell at Residetnial/Retail/School/Hospital locations
I would like to put it in a table so that users can click on a figure and the rest of the report will focus on their choice eg if they click on the Product A figure, the map would show which customers sell Product A.
I thought about the Split Column tool using ; as the Delimiter but I think that would create a lot of columns and data would be disorganised eg first column for Customer 1 would show Product A but first column for Customer 3 would show Product B. Is this possible?
If you want to reach this table start from the first one, and exclude the customer and location columns,
1. Split the Products column by rows
Group by the same column with the defaut config
Did it work ? 👍 A kudos would be appreciated 📢 Mark it as a solution to help spreading knowledge
7 Replies
Hi mike_newbs
I need to understand something before I suggest a solution.
Customer Products Locations Customer 1 Product A; Product B; Product C; Product D Residential; Retail; School; Hospital Does the above row mean Product A was sold in 4 locations, Product B was sold also in 4 locations and so on?
Did it work ? 👍 A kudos would be appreciated 📢 Mark it as a solution to help spreading knowledgeHello DataVitalizer
Apologies I wasn't clear.
It means Customer 1 sells products A, B, C and D in residential, retail, School and hospital locations.
Its counting how many products are sold, it is showing what products the customer are trained to sell and which locations they are trained to sell at.
I hope that clarifies but please feel free to ask any other questions.
Kind regards
Michael
Would this output answer your need? (I kept only two customers for the example)
Customer Product Location Customer 1 Product A Residential Customer 1 Product A Retail Customer 1 Product A School Customer 1 Product A Hospital Customer 1 Product B Residential Customer 1 Product B Retail Customer 1 Product B School Customer 1 Product B Hospital Customer 1 Product C Residential Customer 1 Product C Retail Customer 1 Product C School Customer 1 Product C Hospital Customer 1 Product D Residential Customer 1 Product D Retail Customer 1 Product D School Customer 1 Product D Hospital Customer 2 Product A Residential Customer 2 Product A Retail Customer 2 Product A Hospital Customer 2 Product D Residential Customer 2 Product D Retail Customer 2 Product D Hospital
