Forum Discussion
Calculate loyal customers
Hi
i need help, i will try to explain my problem i hope someone can understand
i have a table with many costumers and many products
situation number one:
a costumer who buys many products in same category, let's say he buys milk from a brand and another brand too so we can say he is not loyal to a brand he buys whatever milk he find on market
situation number two:
customers who buy only one brand of milk so he is loyal to that brand he only buys that brand and if he doesn't find it on the market, he doesn't buy anything else
i want to calculate the costumer who bought only one brand and no other and calculate too the ones who buys many brand
PS: milk is just an exemple we have many products and many categories.
excuse my english
Best Regards.
ID Name - category - products
1 jhon milk milk brand 1
1 jhon milk milk brand 2
1 mary milk milk brand 1
1 mary milk milk brand 1
1 jhon milk milk brand 2
1 jhon milk milk brand 2
1 mary milk milk brand 1
1 mary milk milk brand 1
1 jhon milk milk brand 3
maybe you can create a new column
loyalty = if(CALCULATE(DISTINCTCOUNT('Table'[products]),ALLEXCEPT('Table','Table'[ID Name],'Table'[category]))=1,"Y","N")
5 Replies
ryan_mayuSuper User
maybe you can create a new column
loyalty = if(CALCULATE(DISTINCTCOUNT('Table'[products]),ALLEXCEPT('Table','Table'[ID Name],'Table'[category]))=1,"Y","N")thank you it works like a charm, with some modifications because my table is more complicated than the one gave you but it really works thank you again .
- ryan_mayu
you are welcome
- Ahmedx
I corrected my file, please download it again
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
- Ahmedx
also to calculate the COUNT you can do this:
LoyalCount = SUMX ( FILTER ( VALUES('Table'[Name]), [number of products] = 1 ), 1 ) DisLoyalCount = SUMX ( FILTER ( VALUES('Table'[Name]), [number of products] > 1 ), 1 )
