Need Measure or column(Ranking the Measure values)

[NavaneethaRaju]

Hi, 
I want to rank the measure value respective of column. i enclosed the sample data. can you please suggest any measure or column to rank the measure values. And based on that rank i want apply the specific colors over there. I enclose one format i want to my result would be like  that. 

% of usage = no procedures/Total procedures.
 

Tag ID	Set ID	Set Description	System	Tag Owner	Order Owner	No. Of Procedures	Total Procedures	% Of Usage	Rank
297	965.905	COL	COL	CC	GN	5	7	71%	1
297	965.905	COL	COL	CC	SL	2	7	29%	2
907	932.903	MARS	MARS	CS	CS	2	5	40%	2
907	932.903	MARS	MARS	CS	IH	3	5	60%	1
1053	904.907	PRES	PRES	AM	AM	4	8	50%	1
1053	904.907	PRES	PRES	AM	PP/AH	4	8	50%	1

 Please advice. 

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

You can also check the conditinoal formatting for the font color as well.

Percent of usage measure: = 
AVERAGEX( Data, Data[No. Of Procedures] / Data[Total Procedures] )

Rank measure: =
RANKX (
    FILTER ( ALL ( Data ), Data[Tag ID] = MAX ( Data[Tag ID] ) ),
    [Percent of usage measure:],
    ,
    DESC
)

[jcalheir]

Hi

DAX has a native RANKX Function you can use to rank table columns or measures.

Check out this video

https://www.youtube.com/watch?v=3u0WJFKxGJk

and further documentation on RANKX function

https://docs.microsoft.com/pt-pt/dax/rankx-function-dax

Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[amitchandak]

@NavaneethaRaju ,

Rank = rankx(filter( allselected(Table[Tag ID], Table[Order Owner]), [Tag ID] =max(Table[Tag ID]) ), [Procedures % ],,desc,dense)

You can use color measure for conditional formatting and use that using field value option

example

Switch(true(),
[SLA] = 1 , "Green" ,
[SLA] >= .5 , "Amber" ,
"Red "
)
)

How to do conditional formatting by measure and apply it on pie?
https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
https://community.powerbi.com/t5/Community-Blog/Power-BI-Conditional-formatting-the-Pie-Visual/ba-p/...