Custom calculation based on product categories

[IvanMislav]

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:

• 100% of the customers purchased FOOD
• 50% of the customers purchased DRINKS
• 0% of the customers purchased COSMETICS

It doesnt matter if a customer bought 1 or more products from the same category.

Thank you.

[v-jingzhang · Accepted 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.

[amitchandak]

@IvanMislav , Try a measure like

divide(distinctCOUNT(Table[customer]), calculate(distinctCOUNT(Table[customer]), allselected(Table)))