Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Myrto
Frequent Visitor

Lost and New Customers based on rank 20 in Power BI

Hi Guys,

I'm pretty new to DAX and I was actually hoping to find answers over the previous posts, but unfortunately it wasn't the case. I hope you can help because I'm kinda going crazy with this topic.

To explain you the context, I'm doing analysis over some transportation companies with hundreds of Business Shippers who achieve more than 100 shipments per day and thousands of Private Shippers with very small inputs. Unfortunately, I don't have in my database the definition of Business Shippers and Private Shippers, so I need to analyse my data based on the TOP 20 shippers. 

I have a table with their average shipments per day over the last 4 weeks, where I added a RANKX measure to filter the top 20:

 

Shipper NameAverage Shipments  over 4 WeeksRank Shipper
V21361
U10652
T9543
S9114
R6795
Q6366
P5787
O5118
N5159
M46010
L33111
I21412
H23213
G21214
F18615
E16716
D14017
C9918
B7919
A6020

 

Now, what I need to do is to create a table showing for the last given week if I have a new customer, a lost one or a custemer who came back. This actually becomes complicated when I want to filter this information based on the descending rank of my customers as per previous table because I want to get the information only if the number of shipments achieved or lost is equal or higher than the average of my total shipments Rank 20 (in the exemple above, this average is 60 shipments). So, based on the following data, my new visual shoud show me Customer T as LOST, Q as NEW and M as RETURNING :

 

Shipper NameW1W2W3W4Comment
V534534534534 
U266266266266 
T318318318 LOST
S228228228228 
R170170170170 
Q   636NEW
P145145145145 
O128128128128 
N129129129129 
M230  230RETURNING
L83838383 
I54545454 
H58585858 
G53535353 
F47474747 
E42424242 
D35353535 
C25252525 
B20202020 
A15151515 

 

I don't make it to find a solution, but I guess for some of you it should be simple. Can anyone help please?

3 REPLIES 3
Myrto
Frequent Visitor

Hi @v-yilong-msft ,

thanks for the suggestion, but as I explained in my first post, the case is much more complicated than this because I do not deal only with 20 shippers, but with thousands. The filter I gave is based on their ranking from 1 (the best shipper) to 20. Obviously this TOP 20 can change over the weeks according to the shipments, that's why I need to create a rule (the rule of the average of shipper # 20). I have created the following measure that calculate the average of shipments achieved by shipper ranked 20:

Average Shipments Client ranked 20 = IF([Rank Clients]=20,[Average Shipments over 4 Weeks])
Then I created the following measure to select my NEW, RETURNING and LOST Customer : 
New, Lost & Returning Customer Hebdo =
VAR ShpmCurrWeek = CALCULATE(
    [Shipments Hebdo par jour],
    DATESBETWEEN(DIM_DAY[DATE_REF],
    [Last Given Date Volumes Hebdo],
    DATE(YEAR([Last Given Date Volumes Hebdo]),MONTH([Last Given Date Volumes Hebdo]),DAY([Last Given Date Volumes Hebdo])+7)
    )
)
VAR ShpmBeforeCurrentWeek = CALCULATE(
    [Shipments Hebdo par jour],
    FILTER(DIM_DAY,
    DIM_DAY[DATE_REF] < [Last Given Date Volumes Hebdo]
    )
)
VAR ShpmOneWeekBefore = CALCULATE(
    [Shipments Hebdo par jour],
    DATESBETWEEN(DIM_DAY[DATE_REF],
    DATE(YEAR([Last Given Date Volumes Hebdo]),MONTH([Last Given Date Volumes Hebdo]),DAY([Last Given Date Volumes Hebdo])-7),
    DATE(YEAR([Last Given Date Volumes Hebdo]),MONTH([Last Given Date Volumes Hebdo]),DAY([Last Given Date Volumes Hebdo])-1)
    )
)
RETURN

IF(
    ShpmCurrWeek <> 0,
    IF(ShpmCurrWeek >= [Average Shipments Client ranked 20],
        IF(ShpmBeforeCurrentWeek = 0,
        "NEW CUSTOMER"),
        IF(ShpmOneWeekBefore = 0,
        "RETURNING CUSTOMER")
    ),
    "LOST CUSTOMER"
)
 
The result doesn't match, as it's giving me all shippers above the avarage as NEW and the rest as LOST. 
I believe the problem is the measure "Average Shipments Client ranked 20" because if I drag it into a card, the result will be blank, as instead if I put it in a table context with the list of shippers, it appears on the line of that shipper ranked 20.
v-yilong-msft
Community Support
Community Support

Hi @Myrto ,

First I created the two tables you provided.

vyilongmsft_0-1708568845224.png

vyilongmsft_1-1708568867256.png

Then I created a new column to display the relevant Comment you need.

Comment =
VAR _A = 'Table'[W4]
VAR _B =
    RELATED ( 'Table (2)'[Average Shipments  over 4 Weeks] )
RETURN
    SWITCH ( TRUE (), _A >= _B, "New", _A = 0, "Lost", _A < _B, "RETURNING" )

vyilongmsft_2-1708569291426.png

 

 

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

lbendlin
Super User
Super User

You need to decouple your ranking from your snapshots.

 

- define the ranking for the latest snapshot

- for these 20 companies, retrieve the data for the previous snapshots (if you want, with their respective rankings in those)

 

This will give you all the changes for these accounts but it will also have big gaps as there could be other companies that were big in the prior snapshots and had nothing in the latest.

 

There are graphical ways around that dilemma.  The most popular one is the Ribbon Chart.  You may want to try that one out. Or you can go with the late great Hans Rosling and use a time play axis.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors