cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
UM4R
Frequent Visitor

How to calculate number/percentage of customers who have purchased X amount of products

i have some data and i want to show in graph form to show the number of customers and how many different products they have purchased

 

example data below

 

ID

Product Purchased

1

A

2

A

2

B

3

A

3

B

3

C

4

A

5

B

5

C

 

 

Results

2 bought 1 product

 

2 bought 2 products

 

1 bought 3 products

 

thanks

 

1 ACCEPTED SOLUTION

@UM4R

 

Try this calculated TABLE

 

From the Modelling Tab>>>NEW TABLE

 

Table =
SUMMARIZE (
    SUMMARIZE (
        TableName,
        TableName[ID],
        "No of Products", DISTINCTCOUNT ( TableName[Product Purchased] )
    ),
    [No of Products],
    "No of Customer", DISTINCTCOUNT ( TableName[ID] )
)
Regards
Zubair

Please try my custom visuals


View solution in original post

4 REPLIES 4
ricardocamargos
Continued Contributor
Continued Contributor

Hi @UM4R,

 

You can use either DISTINCTCOUNT formula to calculate distinct products bought per customers or the quick measure COUNT (Distinct) by clicking on the arrow after drop the field on "Value".

 

If I understood what you wanna show, it's a dynamic segmentation, Marco Russo and Alberto Ferrari have a article about it: 

https://www.daxpatterns.com/dynamic-segmentation/

 

Thanks,

 

Ricardo

 

@UM4R

 

Try this calculated TABLE

 

From the Modelling Tab>>>NEW TABLE

 

Table =
SUMMARIZE (
    SUMMARIZE (
        TableName,
        TableName[ID],
        "No of Products", DISTINCTCOUNT ( TableName[Product Purchased] )
    ),
    [No of Products],
    "No of Customer", DISTINCTCOUNT ( TableName[ID] )
)
Regards
Zubair

Please try my custom visuals


View solution in original post

thanks @Zubair_Muhammad  it has seemed to have done the trick 

 

@ricardocamargos thanks for the link, it has a lot of useful information will look in more detail when i have some free time

@UM4R

 

Noofproducts.png

Regards
Zubair

Please try my custom visuals


Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors