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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

Calculate how many consecutive weeks customer has been Top 20

Hello all,

 

I need to calculate Top 20 Customers based on the AmountTotal and I want to calculate for how many consecutive weeks each customer is Top 20 that too all in one virtual table.

Here is my sample data.
CustomerIDAmountTotal

7-11 Direct Centre148548
711-2871008
711-53749723
711-11343953
711-24434346
711-25333025
711-08232104
711-4230873
711-79030615
711-15430577
711-18227751
711-78027397
711-16826511
711-36424861
711-23423899
711-52222797
711-12321944
711-29820799
711-12519744
711-77519207
711-1418751
711-47218403
711-5017471
711-39217387
711-32317319
711-46216586
711-7116412
711-53315922
711-80615738


Your help will be appreciated thanks. Smiley Happy

Cheers. 

2 REPLIES 2
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @Tejaswini_Dahat ,

With your data sample, we could get the top20 based on the AmountTotal with the measure or the calculated column below.

Column = RANKX('Table1','Table1'[Amount Total],,DESC)
Measure = RANKX(ALL('Table1'),CALCULATE(MAX('Table1'[Amount Total])),,DESC)

Here is the output.

Untitled.png

However, I have a little confused about that you want to calculate for how many consecutive weeks each customer is Top 20. 

It seems that you do no that date columns in your data sample firstly. In addition, how many consecutive weeks do you want to calculate? 

If it is convenient, could you give more details and your desired output so that we could help further on it.
Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello @v-piga-msft ,


Actually, I wanted a different output. I want to count the number of Top 20 that each customer has come across. In the sample output, I have only taken Top 5, 10 customers and 2 weeks for easy understanding.

Consider the below screenshot

Capture.PNG

As you can see Customers are weekly ranked based on the Total Amount (A.k.a AmountTotal in the original post) called Rank. Now I want a column that will check whether the customer has been in Top 5 or not. If it has been in Top 5 in week 1 and also in week 2, then the customer has been in Top 5 for 2 weeks consecutively (i.e the count).

 

However, it may happen that customer may lose its Top 5 streak (i.e Customer coming in Top 8 instead for a particular week). If it does, then the Top 5 Streak for that customer should reset. 

Similarly, in the original problem, I need to do the same. I need to find customers that are in Top 20 weekly and the number of times they are coming Top 20 for consecutive weeks. 

Here is my sample PBI file. 
Sample PowerBI File


I hope this gives you enough detail.

Cheers,


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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