cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GCGradwell Helper I
Helper I

Average Services per Customer

Hi,

 

I am trying to generate a value which represents the average number of services which each of our customers buy.

 

All of the products we sell fall into 1 of 3 deparments. I want to generate a measure which sums all of the unique customers within each segment together.

 

For clarity:

Thread 3 img.png

 

 

 

In the above example, Customer 100 has bought 2 services from 1 department, 101 has bought 2 services, 1 each from different departments, 102 has bought 1 service and 103 has bought 2 from the same department.

 

In this case, there are 4 overall unique customers (100, 101, 102 and 103) and 5 unique customers by department (100A, 101B, 101C, 102C and 103C).

 

To determine average number of services per customer, I want to divide the sum of each total of unique customers by department by the number of overall unique customers.

 

5/4 = 1.25 average services per cusomer

 

 

Thread 3 img 2.png

 

 

 

I have created a look-up heirarchy in PBI using relationships where the customer ID and product are in the same table and the product's relationship to class and department are linked using relationships.

 

Each product belongs to a Class and each class belongs to a department (e.g. handset 2 is in the iPhone class which is in the Smartphone department). In Power BI, the products have a relationship to a different table, where each product's class is specified. The product class has a relationship to a third table where the department of each product class is specified.

 

I have generated the measure for overall unique customers (DISTINCTCOUNT of customer ID) but do not know how to create a measure for the unique customers by department.

 

Thanks in advance,

 

Greg

1 ACCEPTED SOLUTION

Accepted Solutions
GCGradwell Helper I
Helper I

Re: Average Services per Customer

Created a new column where I used the CONCATENATE function to combine customer ID and department so that each time a customer purchased an item from different departments, it would be a unique ID-department code, but if they bought multiple products from the same departments, it would generate multiple identical ID-department codes.

 

I then created a new measure using DISTINCTCOUNT function on this concatenated column.

 

Finally, I created a new measure of Average Services per Customer = Unique customers/Unique customers by department

 

Thank you for your help.

 

Greg

View solution in original post

2 REPLIES 2
Microsoft Eric_Zhang
Microsoft

Re: Average Services per Customer

@GCGradwell

 

According to the relationship mapping, have you tried to create a calculated column in the Invoices table?

 

 

Department = RELATED('Product Class'[DepartMent])

It would return the related department, then you can use DISTINCOUNT('Invoices'[Department]).

 

GCGradwell Helper I
Helper I

Re: Average Services per Customer

Created a new column where I used the CONCATENATE function to combine customer ID and department so that each time a customer purchased an item from different departments, it would be a unique ID-department code, but if they bought multiple products from the same departments, it would generate multiple identical ID-department codes.

 

I then created a new measure using DISTINCTCOUNT function on this concatenated column.

 

Finally, I created a new measure of Average Services per Customer = Unique customers/Unique customers by department

 

Thank you for your help.

 

Greg

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors