Categorizing a measure column

[Anonymous]

Hi,

I have 2 columns. Employees and Total Guests to each employees.

See below.

Employee	Total Guests
abc1	99
abc2	91
abc3	73
abc4	26
abc5	59
abc6	49
abc7	39
abc8	17
abc9	32
abc10	68
abc11	93
abc12	63
abc13	66
abc14	76
abc15	66
abc16	47
abc17	28

I want to color code on Total guests such that Top 1% should be in blue color.

Top33 - Green

Middle33- Yellow

Bottom33- Red

Please let me know the dax calculation that can be later used in conditional formatting.

On a sideline, please also let me know how can I make calculated columns in azure analytics server as Power BI doesnt allow me to create calculated columns.

[Icey · Accepted solution]

Hi @Anonymous ,

Is this what you want? 

If it is, try this:

1. Create measures.

Rank =
RANKX (
    ALLSELECTED ( 'Table' ),
    CALCULATE ( MAX ( 'Table'[Total Guests] ) ),
    ,
    DESC,
    DENSE
)

Percent = DIVIDE ( [Rank], MAXX ( ALL ( 'Table' ), [Rank] ) )

2. Set conditional formating.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Check

https://community.powerbi.com/t5/Desktop/Calculating-the-percentile-for-a-set-of-data/td-p/250581

After getting percentile as column you should able to color

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...