how to highlight top n value in table visual and change based on slicer

[lilynguyen]

yDear,

I have a detailed data table like this

Warehouse	Order ID	weight	Date	Over / not
A	1	12	1/2/2002	Over
B	2	35	1/2/2002	Not
C	3	76	1/2/2002	Not
D	4	85	1/2/2002	Over
E	5	92	1/2/2002	Over
A	6	32	2/2/2020	Over
B	7	95	2/2/2020	Not
D	8	1	2/2/2020	Over
D	9	3	2/2/2020	Over
C	10	5	2/2/2020	Not

And I created a table visual on report, like this:

Warehouse	Total order ID	Total weight
B	2	130
E	1	92
D	3	89
C	1	81
A	2	44

I can highlight top 3 if I don't add any slicer on report.
When I add some slicer on report, like: Date Filter, Over / not Filter,... I tried to use RankX but when I choose Selected all on any filter, it doesn't work as I think.

Any solution idea for me?
Thanks.

[v-yangliu-msft]

Hi  @lilynguyen ,

Here are the steps you can follow：

1. Create measure.

Total weight_sample =
SUM('Table'[weight])

rank =
RANKX(ALLSELECTED('Table'),[Total weight_sample],,DESC)

Total order ID =
var _count= COUNT('Table'[Order ID])
return
IF(HASONEFILTER('Table'[Over / not])||HASONEFILTER('Table'[Date]),
_count,
IF([rank]<=3,_count,BLANK()))

Total weight =
IF(HASONEFILTER('Table'[Over / not])||HASONEFILTER('Table'[Date]),
[Total weight_sample],
IF([rank]<=3,[Total weight_sample],BLANK()))

2. Put the total order ID and total weight in the table

3. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

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

[amitchandak · Accepted solution]

@lilynguyen , Create TOPN Measure an then create a colot measure on top of it

https://youtu.be/QIVEFp-QiOk

Top 3 City Rank = CALCULATE([Sales],TOPN(3,all(Geography[City]),[Sales],DESC),VALUES(Geography[City Id]))

color = if(isblank([Top 3 City Rank]), "white", "green")

use it conditional formtting using "field" in coditional formatting

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values