Ranking by Multiple Criteria

[Anonymous]

Respected All, I am trying to rank my data in which there are following fields Date,Region, Area, Store Name, Store Tier & Sales I want to get rank against Sales but Date wise > Region Wise > Area Wise > Store Tier Wise Like which store of which tier in which region performed well.

[amitchandak]

@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

[Anonymous]

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.

[harshnathani]

Hi @Anonymous ,

Create a measure.

Datewise Sales =

RANKX(FILTER(ALLSELECTED('Table'),'Table'[Date] = MAX('Table'[Date])),CALCULATE(SUM('Table'[Sales])))

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

Respected Natahni,

I really appreciate your effort but i need column for this.

[harshnathani]

Hi @Anonymous ,

For a Calculated Column

You can use

Column =

RANKX(FILTER('Table1','Table1'[Date] = EARLIER('Table1'[Date])),Table1[Sales])

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

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

[harshnathani · Accepted solution]

Hi @Anonymous ,

You are ranking the Stores according to Sales which belong to the Same Tier for a particular Date.

Column =

RANKX(FILTER('Table1','Table1'[Date] = EARLIER('Table1'[Date]) && Table1[Store Tier] = EARLIER(Table1[Store Tier])),Table1[Sales])

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

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.

[Anonymous]

Respected Harshnathani,

This works but only for date wise sales Ranking, it doesn't includes Tier. 😞 

[Anonymous]

Respected Amit,

Is there any way to rank the data using sales but date wise and tier wise only ??