In this video we will use Power BI and I will show you how to create a new column with a CountIF type formula using CountA. CountA is count all and in this formula we will look for a certain conditions to be met - where the current row item equals the previous row item. When used with the data in an ascending order, this will identify repeats. The data we are working with is real hardware store (chain) data for a mailer coupon.
This coupon has 6 different offers (2 per group based on customer spend and loyalty metrics). We want to identify the repeat purchasers as these are one time use coupons and should not be used more than one time per customer.
I will show you the DAX formula and where to add it - new column in the table - Data layer, not model. Then I will show you how to add each of the visuals to end up with an insightful dashboard that shows exactly what coupons and groups had the repeat purchases, how many repeats there were and the total coupon redemptions from participants for reference and comparison. From this you could easily get percentage of potential abuse and more.
This video shows every step and the complete code you need to be able to easily replicate this yourself on your own data.
I hope you found this interesting and helpful. Please take a moment to subscribe, like and share - that way you won't miss any of my latest videos when I publish them.
Thanks again and God bless!