Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
mike_newbs
Frequent Visitor

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

CustomerProductsLocations
Customer 1Product A; Product B; Product C; Product DResidential; Retail; School; Hospital
Customer 2Product A; Product DResidential; Retail; Hospital
Customer 3Product B; Product CSchool; Hospital
Customer 4Product A; Product B; Product DResidential; 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. 

mike_newbs_0-1690805480402.png

 

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?

1 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 

othy_bi_0-1690808373391.png

Group by the same column with the defaut config

othy_bi_1-1690808449957.png

Did it work ?  👍 A kudos would be appreciated ‌‌📢 Mark it as a solution to help spreading knowledge

 

View solution in original post

7 REPLIES 7
DataVitalizer
Super User
Super User

Hi @mike_newbs 

I need to understand something before I suggest a solution.

CustomerProductsLocations
Customer 1Product A; Product B; Product C; Product DResidential; 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

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

Would this output answer your need? (I kept only two customers for the example)

 

CustomerProductLocation
Customer 1Product AResidential
Customer 1Product ARetail
Customer 1Product ASchool
Customer 1Product AHospital
Customer 1Product BResidential
Customer 1Product BRetail
Customer 1Product BSchool
Customer 1Product BHospital
Customer 1Product CResidential
Customer 1Product CRetail
Customer 1Product CSchool
Customer 1Product CHospital
Customer 1Product DResidential
Customer 1Product DRetail
Customer 1Product DSchool
Customer 1Product DHospital
Customer 2Product AResidential
Customer 2Product ARetail
Customer 2Product AHospital
Customer 2Product DResidential
Customer 2Product DRetail
Customer 2Product DHospital

I am looking for something more like:

 

ProductsNumber of sellers
Product A3
Product B2
Product C3
Product D2

 

Kind regards

 

Michael

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 

othy_bi_0-1690808373391.png

Group by the same column with the defaut config

othy_bi_1-1690808449957.png

Did it work ?  👍 A kudos would be appreciated ‌‌📢 Mark it as a solution to help spreading knowledge

 

Thank you @DataVitalizer  That worked perfectly.

I am glad I was able to help

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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