Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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.
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
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |