Organising cells with multiple data values

[mike_newbs]

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?

[DataVitalizer]

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 knowledge

[mike_newbs]

Hello @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

[DataVitalizer]

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

[mike_newbs]

I am looking for something more like:

Products	Number of sellers
Product A	3
Product B	2
Product C	3
Product D	2

Kind regards

Michael

[DataVitalizer · Accepted solution]

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

[mike_newbs]

Thank you @DataVitalizer  That worked perfectly.

[DataVitalizer]

I am glad I was able to help