Forum Discussion
Analytic Group by Customer based on their value
Hi Guru's
Need assistance please. I am trying to create an Analytic Grouping to Class my Customers based on their profit over the last 12 months.
Below is the data structure and an example of the two columns I need highlighted in Green.
I need to create two columns;
1. return the value of customer profit if the transaction was made within the last 12 months, if the date of transaction is not within the last 12 months then return 0.
2. Give each customer a class based on their "last 12 month profit" spend. I have included in my screenshot the formula as it would be done in excel.
=IF(E2>=1000,"AA",IF(AND(E2<1000,E2>=250),"A",IF(AND(E2<250,E2>=50),"B",IF(AND(E2<50,E2>=5),"C",IF(AND(E2<5,E2>=0.5),"D",IF(E2<0.5,"E","N/A"))))))
Hi Anno2019 ,
To create a measure instead of a calculated column.
Customer Class 1 = VAR a = SUM ( 'Shipment Level Data'[Profit for last 12 months] ) RETURN IF ( ISBLANK ( a ), "N/A", SWITCH ( TRUE (), a >= 1000, "AA", a < 1000 && a >= 250, "A", a < 250 && a >= 50, "B", a < 50 && a >= 5, "C", a < 5 && a >= 0.5, "D", a < 0.5, "E" ) )
4 Replies
Hi Anno2019 ,
Based on your data, we can create two calculated column as below.
Profit for last 12 months = VAR last12month = TODAY () - 365 RETURN IF ( 'Table'[Date] >= last12month, 'Table'[Profit], BLANK () )Customer Class = IF ( ISBLANK ( 'Table'[Profit for last 12 months] ), "N/A", SWITCH ( TRUE (), 'Table'[Profit for last 12 months] >= 1000, "AA", 'Table'[Profit for last 12 months] < 1000 && 'Table'[Profit for last 12 months] >= 250, "A", 'Table'[Profit for last 12 months] < 250 && 'Table'[Profit for last 12 months] >= 50, "B", 'Table'[Profit for last 12 months] < 50 && 'Table'[Profit for last 12 months] >= 5, "C", 'Table'[Profit for last 12 months] < 5 && 'Table'[Profit for last 12 months] >= 0.5, "D", 'Table'[Profit for last 12 months] < 0.5, "E" ) )Hi v-frfei-msft
Thank you so much for the assistance, it worked up to a point. The minute the customer appears twice though it is not aggregating....
I have shared the link to my example file. Customer Name is Duplicating. Not sure how to fix.
https://www.dropbox.com/s/6un3qk9gkzbj7lw/Testing%20Shipment%20Data%20Customer%20Class.pbix?dl=0
Hi Anno2019 ,
To create a measure instead of a calculated column.
Customer Class 1 = VAR a = SUM ( 'Shipment Level Data'[Profit for last 12 months] ) RETURN IF ( ISBLANK ( a ), "N/A", SWITCH ( TRUE (), a >= 1000, "AA", a < 1000 && a >= 250, "A", a < 250 && a >= 50, "B", a < 50 && a >= 5, "C", a < 5 && a >= 0.5, "D", a < 0.5, "E" ) )
