CUSTOMER SEGMENTATION AGAINST ORDER RANKING

Question

Hi Team,I have two tables - Customers and Orders. These tables are connected via the customer_id via a one-to-many relationship.&nbsp;I created a calculated column in my customer's table named Redeemer_Status&nbsp;where customers are segmented into two - REDEEMER1 and REDEEMER2.&nbsp;Redeemer_Status = VAR _CustomerID = customers[id]VAR _HC_1 = CALCULATE(MAX(orders[with HC]), FILTER(orders, orders[_Order Ranking] = 1 &amp;&amp; orders[customer_id] = _CustomerID))VAR _SignUpOrigin_1 = CALCULATE(MAX(orders[SignUp_Origin]), FILTER(orders, orders[_Order Ranking] = 1 &amp;&amp; orders[customer_id] = _CustomerID))VAR _HC_2 = CALCULATE(MAX(orders[with HC]), FILTER(orders, orders[_Order Ranking] = 2 &amp;&amp; orders[customer_id] = _CustomerID))VAR _SignUpOrigin_2 = CALCULATE(MAX(orders[SignUp_Origin]), FILTER(orders, orders[_Order Ranking] = 2 &amp;&amp; orders[customer_id] = _CustomerID))RETURNIF(&nbsp; &nbsp; _HC_1 = 1 &amp;&amp; (_SignUpOrigin_1 = "WHATSAPP_BOT" || _SignUpOrigin_1 = "MOBILE_APP"),&nbsp; &nbsp; "REDEEMER1",&nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; _HC_1 = 0 &amp;&amp; (_SignUpOrigin_1 = "WHATSAPP_BOT" || _SignUpOrigin_1 = "MOBILE_APP") &amp;&amp; _HC_2 = 0 &amp;&amp; (_SignUpOrigin_2 = "WHATSAPP_BOT" || _SignUpOrigin_2 = "MOBILE_APP"),&nbsp; &nbsp; &nbsp; &nbsp; "NON-REDEEMER",&nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; _HC_2 = 1 &amp;&amp; (_SignUpOrigin_2 = "WHATSAPP_BOT" || _SignUpOrigin_2 = "MOBILE_APP") &amp;&amp; _HC_1 = 0 &amp;&amp; (_SignUpOrigin_1 = "WHATSAPP_BOT" || _SignUpOrigin_1 = "MOBILE_APP"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "REDEEMER2",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BLANK()&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; ))I created a stacked column chart where the column _Order Ranking in my orders table serves as the x-axis and customer_id (Unique) in my Y-axis. I then use the&nbsp;Redeemer_Status column as the legend but filtered it with only the REDEEMER1 and REDEEMER2.&nbsp;I am so confused as to why there are REDEEMER2 that show up in the order ranking 1 instead they should only start to appear in the 2nd order onwards.&nbsp;In this graph, there should be no REDEEMER2 in the column for order rank 1. The 141 REDEEMER2 is incorrect and should not appear there.&nbsp;What did I do wrong?&nbsp;

lbendlin · Answer

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

andrew_na_lang · Answer

Customer Segmentation - based on order rank 1 or rank 2

Hi,

I need help!

So, in my orders table, I created two calculated columns -

_____Redeemer 1 =

VAR CustomerID = orders[customer_id]

VAR _HC = orders[with HC]

RETURN

IF( CustomerID = orders[customer_id] &&

_HC = "Yes" &&

orders[_Order Ranking] = 1 ,

"REDEEMER" ,

""

)

and

_____Redeemer 2 =

VAR CustomerID = orders[customer_id]

VAR _HC = orders[with HC]

RETURN

IF( CustomerID = orders[customer_id] &&

_HC = "Yes" &&

orders[_Order Ranking] = 2 ,

"REDEEMER" ,

""

)

What I am trying to achieve here is:

1. Identify which order the customer sent an HC bag- in the first or second order.
2. Then, marked the unique customer REDEEMER1, REDEEMER2, NON-REDEEMER
REDEEMER1 is if he/she submitted an HCbag in the first order or REDEEMER1
REDEEMER2 is if he/she submitted an HCBag in the second order but no HC bag in the first order.
Lastly, if no HC bag in both the first or second order, Tag him as "NON-REDEEMER".

Ultimately, I need to create a stacked column chart where my x-axis is the order ranking, the y-axis is the distinct count of customer_id, having a legend REDEEMER1, REDEEMER2, and NON-REDEEMER. In the chart, REDEEMER2 should only appear from order_rank 2 onwards, there shouldn't be any REDEEMER2 in Order rank 1.

For some reason, I cannot add the pbix file here.

lbendlin · Answer

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

andrew_na_lang · Answer

andrew_na_lang · Answer

lbendlin&nbsp;here is the link on google sheets -&nbsp;https://docs.google.com/spreadsheets/d/13smuzNornBBnk2-_utR1WdJjLzZBlvWvRjYxJqoFwiw/edit#gid=1387572587&nbsp;

Forum Discussion

CUSTOMER SEGMENTATION AGAINST ORDER RANKING

7 Replies

Related content

Segmentation based on Product List available for each Customer

Customer Segmentation

Customer segmentation

Dynamic Customer Segmentation

filter with segmentation