Count number of customers based on grouping/range

[geotech]

Hello All,

I have read through similar posts and have attempted to implement the solutions for each, but without success. My version is a bit simplier than the other posts, so it's possible I have been over-applying the solutions. I believe I need to implement Dynamic Segmentation, as discribed here.

I have the following table:

Customer Name (or ID)	Project Name
Customer 1	Project 1
Customer 1	Project 2
Customer 2	Project 3
Customer 3	Project 4
Customer 3	Project 5
Customer 3	Project 6
Customer 3	Project 7
Customer 3	Project 8

...continues for the remainder of the data set.

I would like to display the number of customers in a bar or pie chart based on the range the project count per customer fall into (1, 2-4, and 5 or more). I have created a Customer Ranges table that looks like this:

The next step I am not sure of - I can create a measure to count the number of projects per customer, I can also create a measure to place the customer into a group (1, 2-4, or 5 or more). Instead, I think I need to create a COUTNROWS function that uses the Customer Ranges table as filter, however I'm not sure how to write this measure.

Your help is greatly appreciated, thank you.

[az38]

hi @geotech 

try new measure

Customer Range = switch (
TRUE();
calculate(DISTINCTCOUNT('Table'[Project Name]);ALLEXCEPT('Table';'Table'[Customer Name (or ID)]))=1;"1";
and(calculate(DISTINCTCOUNT('Table'[Project Name]);ALLEXCEPT('Table';'Table'[Customer Name (or ID)])) > 1;calculate(DISTINCTCOUNT('Table'[Project Name]);ALLEXCEPT('Table';'Table'[Customer Name (or ID)])) < 5); "2 to 4"; 
calculate(DISTINCTCOUNT('Table'[Project Name]);ALLEXCEPT('Table';'Table'[Customer Name (or ID)])) >= 5;"5 or more"
)

do not hesitate to give a kudo to useful posts and mark solutions as solution
Linkedin

[geotech]

Thank you, @az38 - that allows me to group each customer by the number of projects per customer:

What I would like to do is plot this data on a bar/pie chart to see the number of customers that fall into each range - is that possible to do using this measure?

[az38 · Accepted solution]

So, @geotech 

for bar or pie chart use a technique from here https://stackoverflow.com/questions/52169206/dax-grouping-by-a-measure-result

Create a dimention table Customer Ranges with one column

Customer Range

1

2 to 4

5 or more

next, add a measure to this new table

Measure = 
VAR S = summarize('Table';
'Table'[Customer Name (or ID)];
"CustRange";switch(
true();
countrows('Table')>=5;"5 or more";
countrows('Table')<2;"1";
"2 to 4")
) return
 sumx(S ;if([CustRange]=SELECTEDVALUE('Customer Ranges'[Customer Range]);1;0))

add to a legend field 'Customer Ranges'[Customer Range] and as a Value - this new measure

do not hesitate to give a kudo to useful posts and mark solutions as solution
Linkedin

[geotech]

@az38, thank you!