cancel
Showing results for
Did you mean:
Frequent Visitor

## 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 Centre 148548 711-28 71008 711-537 49723 711-113 43953 711-244 34346 711-253 33025 711-082 32104 711-42 30873 711-790 30615 711-154 30577 711-182 27751 711-780 27397 711-168 26511 711-364 24861 711-234 23899 711-522 22797 711-123 21944 711-298 20799 711-125 19744 711-775 19207 711-14 18751 711-472 18403 711-50 17471 711-392 17387 711-323 17319 711-462 16586 711-71 16412 711-533 15922 711-806 15738

Your help will be appreciated thanks.

Cheers.

2 REPLIES 2
Community Support Team

## Re: Calculate how many consecutive weeks customer has been Top 20

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.

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.
Highlighted
Frequent Visitor

## Re: Calculate how many consecutive weeks customer has been Top 20

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

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,

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 47 members 1,131 guests
Recent signins: