topic Re: RANK the measure in DAX Commands and Tips

RANK the measure

FreedJustine — Thu, 27 Feb 2020 11:33:51 GMT

Hi!

Please help!

I created a measure using switch

'Ave sales size =

SWITCH(TRUE(),

[Average Sales]<100,"Small",

[Average Sales]>1000,"Large",

[Average Sales]>=100 && [Average Sales] <=1000,"Medium")'

I want to rank the client base on total sales per ave sales size category

in excel i can simply use countifs

COUNTIFS(Column Ave Sales size, Ave Sales Size,">"&Total Sales)+1

below is my sample. Thank you in advance.

Client	Jan	Feb	Mar	Total Sales	Ave Sales	Ave Sales size	Total Sales Rank per Ave Sales Size
A004	1,500	2,000	1,000	4,500	1,500	Large	1
A006	10	1,000	2,000	3,010	1,003	Large	2
A002	500	400	100	1,000	333	Medium	3
A003	1,000	999	800	2,799	933	Medium	1
A007	-	500	1,000	1,500	500	Medium	2
A001	80	100	80	260	87	Small	1
A005	10	10	20	40	13	Small	2

Re: RANK the measure

Anonymous — Thu, 27 Feb 2020 12:02:26 GMT

Hi there. If you don't get any answers within a reasonable amount of time, that means your description of the problem is most likely not being understood.

Just a hint...

Best
D

Re: RANK the measure

FreedJustine — Thu, 27 Feb 2020 12:10:41 GMT

Anonymous

Thank you for a hint.

Is there any ways to rank a calculated measures?

I want to rank base on client column then followed by the measures I created which is 'Ave Sales Size'

Re: RANK the measure

Anonymous — Thu, 27 Feb 2020 12:24:47 GMT

Everything is possible. Which is the good news.

Best
D

Re: RANK the measure

Anonymous — Thu, 27 Feb 2020 12:30:11 GMT

You should create these measures as a first step...

// This replaces your measure from above [Avg Sales Size] = var __avgSales = [Average Sales] return SWITCH(TRUE(), __avgSales > 1000, "Large", __avgSales >= 100, "Medium", "Small") // This measure should be HIDDEN // It should be used in ranking [Avg Sales Size For Ranking] = var __avgSales = [Average Sales] return SWITCH(TRUE(), __avgSales > 1000, 2, __avgSales >= 100, 1, 0 )

Best