Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
FreedJustine
Helper I
Helper I

RANK the measure

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.
 
ClientJanFebMarTotal SalesAve SalesAve Sales sizeTotal Sales Rank per Ave Sales Size
A004              1,500           2,000           1,000           4,500                  1,500Large1
A006                    10           1,000           2,000           3,010                  1,003Large2
A002                 500               400               100           1,000                      333Medium3
A003              1,000               999               800           2,799                      933Medium1
A007                     -                 500           1,000           1,500                      500Medium2
A001                    80               100                 80               260                        87Small1
A005                    10                 10                 20                 40                        13Small2
4 REPLIES 4
Anonymous
Not applicable

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

@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
Not applicable

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
Not applicable

Everything is possible. Which is the good news.

Best
D

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors