How to get top 5, Midle, and Bottm 5

[ushankar]

Hi ,

I have an requirment to acheive 

Top 5 in Green , Bottom 5 in Red and other values in Purple.

I need to acheive this in scatter plot visualization.

Need your help. I have attached the data sheet that i'm using for this requirement. 

Thanks in advance

Product	Sales Volume	Revenue	Rank
A	33.5549	7189386	Top5
B	137.4189	22808754	Top5
C	272.0348	22142154	Top5
D	149.5983	11514751	Top5
E	214.1868	14848040	Top5
F	117.7801	7935856	Normal
G	175.585	10154915	Normal
H	182.6557	10251254	Normal
I	183.7683	10227660	Normal
J	184.8968	10235825	Normal
K	238.4703	12649451	Normal
M	231.635	12076366	Normal
N	161.2197	8377149	Normal
O	210.0946	10492270	Normal
P	197.6389	9818325	Normal
Q	156.3735	7416810	Normal
S	172.3715	8068115	Normal
T	156.649	7205120	Bottom5
U	200.5822	8956852	Bottom5
X	176.8401	7862781	Bottom5
Y	159.6275	7083706	Bottom5
Z	181.7734	8002398	Bottom5

[Mariusz]

Hi @ushankar ,

The below mesure shoud resolve your problem 

Spoiler

RANK = 
VAR P = ALL(Table1[Product]) --Product column
VAR T = RANKX( P, [s] ,,DESC) <= 5 -- replace [s] with your mesure
VAR B = RANKX( P, [s] ,,ASC) <= 5 -- replace [s] with your mesure
RETURN 
SWITCH(
    TRUE(),
    T, 1, --is 1 then conditional format green 
    B, 2, --is 2 then conditional format red 
    3 -- is 3 then conditional format purple
)

Use this mesure as conditonal formating.

Thanks

Mariusz

[TomMartens · Accepted solution]

Hey @ushankar ,

the solution provided by @Mariusz is much more elegant as the calculated column based solution I provided, but nevertheless it should tweaked a little.

I created a little pbix file, the scatter plot provides the same result, but used in a table the measure might create unexpected results as the following pictures shows:

For this reason I recommend to tweak the measure like so:

ms RANK ALL Table = 
VAR P = ALL('Table1') --table instead of Product column
VAR T = RANKX( P, CALCULATE(SUM('Table1'[SN])) ,,DESC) <= 5 -- replace [s] with your mesure
VAR B = RANKX( P, CALCULATE(SUM('Table1'[SN])) ,,ASC) <= 5 -- replace [s] with your mesure
RETURN 
SWITCH(
    TRUE(),
    T, "green", --is 1 then conditional format green 
    B, "red", --is 2 then conditional format red 
    "purple" --3 -- is 3 then conditional format purple
)

Regards,
Tom

[ushankar]

Thanks Tom !

Your solution did really works for me.

[TomMartens]

Hey,

I guess this looks very close to your requirement 😉

To achieve this I created a measure:

vizAid FillColor = 
var thisRank = FIRSTNONBLANK('Table1'[Rank],0)
return
--"red"

SWITCH(
    thisRank
    ,"Top5", "green"
    ,"Normal", "purple"
    ,"Bottom5", "red"
    ,"blue"
)

To work properly it's necessary that the column "Product" is used on field weld "Details" of the Scatter chart visual:

Then I assigned the measure to the data color using conditional formatting:

and finally this:

Hopefully this is what you are looking.

Regards,

Tom

[ushankar]

Hi Tom,

Thanks for your quick reply. I was really quick.

My bad i did not articulate my requirement properly. According to the below table I want to rank on "SN" feild , the Rank column which i have mentioned is just for reference.

Product	Sales Volume	Revenue	SN	Rank
A	33.5549	7189386	214257	Top5
B	137.4189	22808754	165980	Top5
C	272.0348	22142154	81395	Top5
D	149.5983	11514751	76971	Top5
E	214.1868	14848040	69323	Top5
F	117.7801	7935856	67379	Normal
G	175.585	10154915	57835	Normal
H	182.6557	10251254	56123	Normal
I	183.7683	10227660	55655	Normal
J	184.8968	10235825	55360	Normal
K	238.4703	12649451	53044	Normal
L	231.635	12076366	52135	Normal
M	161.2197	8377149	51961	Normal
N	210.0946	10492270	49941	Normal
O	197.6389	9818325	49678	Normal
P	156.3735	7416810	47430	Normal
Q	172.3715	8068115	46807	Normal
R	156.649	7205120	45995	Bottom5
S	200.5822	8956852	44654	Bottom5
T	176.8401	7862781	44463	Bottom5
U	159.6275	7083706	44376	Bottom5
V	181.7734	8002398	44024	Bottom5

[TomMartens]

Hey,

you can create a calculated column using this DAX:

Column = 
var top5Product = 
    SELECTCOLUMNS(
    TOPN(5,
        'Table2'
        ,CALCULATE(SUM('Table2'[SN]))
        ,DESC
        )    
        ,"topNProduec",'Table2'[Product]
    )
var bottom5Product = 
    SELECTCOLUMNS(
        TOPN(5,
        'Table2'
        ,CALCULATE(SUM('Table2'[SN]))
        ,ASC
        )    
        ,"topNProduec",'Table2'[Product]
    )    
return
--countrows(top5Product)

IF('Table2'[Product] in top5Product,"Top5"
    ,IF('Table2'[Product] in bottom5Product, "Bottom5","something else")
)
   

The result will look this:

Please be aware that I changed the value for Product K on purpose to avoid getting trapped by the ordering of product/values 🙂
Then just apply/adapt the solution from my first post.

Regards,

Tom