Forum Discussion

IvanMislav's avatar
IvanMislav
Helper I
5 years ago
Solved

Custom calculation based on product categories

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 CategoryProductPurchased?
Customer 1FoodAppleTRUE
Customer 1FoodPearFALSE
Customer 1DrinksWaterTRUE
Customer 1DrinksTeaTRUE
Customer 1CosmeticsShampooFALSE
Customer 1CosmeticsDeodorantFALSE
Customer 2FoodAppleTRUE
Customer 2FoodPearTRUE
Customer 2DrinksWaterTRUE
Customer 2DrinksTeaFALSE
Customer 2CosmeticsShampooFALSE
Customer 2CosmeticsDeodorantFALSE
…   

 

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.

 

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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.

2 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    5 years ago

    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's avatar
    amitchandak
    Super User
    5 years ago

    IvanMislav , Try a measure like

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