Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm trying to find a way to calculate pecentage of customers that purchased something from a certain group of products. Table example blow:
Customer | Category | Product | Purchased? |
Customer 1 | Food | Apple | TRUE |
Customer 1 | Food | Pear | FALSE |
Customer 1 | Drinks | Water | TRUE |
Customer 1 | Drinks | Tea | TRUE |
Customer 1 | Cosmetics | Shampoo | FALSE |
Customer 1 | Cosmetics | Deodorant | FALSE |
Customer 2 | Food | Apple | TRUE |
Customer 2 | Food | Pear | TRUE |
Customer 2 | Drinks | Water | TRUE |
Customer 2 | Drinks | Tea | FALSE |
Customer 2 | Cosmetics | Shampoo | FALSE |
Customer 2 | Cosmetics | Deodorant | FALSE |
… |
So, I would like to come up with a measure which would calculate percentage of products bought based on category:
It doesnt matter if a customer bought 1 or more products from the same category.
Thank you.
Solved! Go to Solution.
Hi @IvanMislav
Try this measure. According to your sample data, 100% of the customers purchased DRINKS rather than 50%.
Measure =
VAR __allCustomers = CALCULATE(DISTINCTCOUNT('Table'[Customer]),ALL('Table'))
VAR __buyingCustomers = CALCULATE(DISTINCTCOUNT('Table'[Customer]),ALLEXCEPT('Table','Table'[Category]),'Table'[Purchased?]=TRUE())
RETURN
DIVIDE(__buyingCustomers,__allCustomers)+0
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
Hi @IvanMislav
Try this measure. According to your sample data, 100% of the customers purchased DRINKS rather than 50%.
Measure =
VAR __allCustomers = CALCULATE(DISTINCTCOUNT('Table'[Customer]),ALL('Table'))
VAR __buyingCustomers = CALCULATE(DISTINCTCOUNT('Table'[Customer]),ALLEXCEPT('Table','Table'[Category]),'Table'[Purchased?]=TRUE())
RETURN
DIVIDE(__buyingCustomers,__allCustomers)+0
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
@IvanMislav , Try a measure like
divide(distinctCOUNT(Table[customer]), calculate(distinctCOUNT(Table[customer]), allselected(Table)))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |