RANK the measure

[FreedJustine]

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

[Anonymous]

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

[FreedJustine]

@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'

[Anonymous]

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

D

[Anonymous]

Everything is possible. Which is the good news.

Best
D