Hi Vikram Gokhale,
I took a stab at this randomly throughout the day while waiting for other things to run because it seemed a fun challenge. I think I was able to come up with the desired result using several LOD expressions. To make things easier, I ended up consolidating to just a few.
I used Sample - Superstore and in my example Defector: a customer with no sales during month selected but sales in a previous month exceeding $50.
Essentially I had to make two separate Defector Flags - one for Category and one for Product Name (you would replace Category with Product Group here). They are identical except you include [Product Name] for the second Flag:
Then you simply make a new Measure that counts the customers. However, you need to know which to use since it's hierarchical:
Then I was able to build the final table looking at the month selected, the number of customers per category this month, total sales, and number of calculated defectors:
I converted customer count and sales to strings so they'd display as an empty string rather than writing out "Null" when you drilldown to Product Name:
Hope this helps!
Defectors.twbx 1.2 MB
Thanks for your inputs Bryce. Will definitely look into this and see if it works... Thanks again..