Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Solved! Go to Solution.
Hi @Anonymous ,
You are ranking the Stores according to Sales which belong to the Same Tier for a particular Date.
@Anonymous , your measure should have the filters and then create rank on that measure.
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415
This is what i am trying to achieve
Countifs(Date Range,Date,Store Tier Range, Store Tier,Sales Range,">"&SalesValue)+1
=COUNTIFS($A$2:$A$1048573,$A104,$B$2:$B$1048573,$B104,$J$2:$J$1048573,">"&$J104)+1
Using this formula in excel and is giving accurate answer, can include region & area as well
Date | Store Tier | Sales | Ranks |
09/06/2020 | GOLD | 100 | 5 |
09/06/2020 | DIAMOND | 200 | 4 |
09/06/2020 | BRONZE | 300 | 3 |
09/06/2020 | SILVER | 400 | 2 |
09/06/2020 | PLATINIUM | 500 | 1 |
10/06/2020 | GOLD | 100 | 5 |
10/06/2020 | DIAMOND | 200 | 4 |
10/06/2020 | BRONZE | 300 | 3 |
10/06/2020 | SILVER | 400 | 2 |
10/06/2020 | PLATINIUM | 500 | 1 |
Is there any solution that i can get
1- Date wise Tier wise Sales wise Rank
2- Date wise Region wise Sales wise Rank
3- Date wise Area wise Sales wise Rank
I have gone through so many solutions but none of them included date and when i apply that it ranked perfectly but not according to individual date.
Hi @Anonymous ,
Create a measure.
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Respected Natahni,
I really appreciate your effort but i need column for this.
Hi @Anonymous ,
For a Calculated Column
You can use
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
I think i wasn't able to explain my self properly, below is the exact table which em trying to create in BI. The Rank is based on Date, Store, Store Tier against Sales. I would really appreciate if you help me out.
Date | Store | Store Tier | Sales | Ranks |
09/06/2020 | A | BRONZE | 100 | 2 |
09/06/2020 | B | BRONZE | 200 | 1 |
09/06/2020 | A | DIAMOND | 100 | 2 |
09/06/2020 | B | DIAMOND | 200 | 1 |
09/06/2020 | A | GOLD | 100 | 2 |
09/06/2020 | B | GOLD | 200 | 1 |
09/06/2020 | A | PLATINIUM | 100 | 2 |
09/06/2020 | B | PLATINIUM | 200 | 1 |
09/06/2020 | A | SILVER | 100 | 2 |
09/06/2020 | B | SILVER | 200 | 1 |
10/06/2020 | A | BRONZE | 100 | 2 |
10/06/2020 | B | BRONZE | 200 | 1 |
10/06/2020 | A | DIAMOND | 100 | 2 |
10/06/2020 | B | DIAMOND | 200 | 1 |
10/06/2020 | A | GOLD | 100 | 2 |
10/06/2020 | B | GOLD | 200 | 1 |
10/06/2020 | A | PLATINIUM | 100 | 2 |
10/06/2020 | B | PLATINIUM | 200 | 1 |
10/06/2020 | A | SILVER | 100 | 2 |
10/06/2020 | B | SILVER | 200 | 1 |
Hi @Anonymous ,
You are ranking the Stores according to Sales which belong to the Same Tier for a particular Date.
Respected Harshnathani,
Thanks alot for providing such a quick and accurate response i'll always look forward to learn more things from you. Stay Hapy Stay Safe. Thanks Alot.
Respected Harshnathani,
This works but only for date wise sales Ranking, it doesn't includes Tier. 😞
Respected Amit,
Is there any way to rank the data using sales but date wise and tier wise only ??
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
68 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |