Top 10 Rank based on user filter selection

[Anonymous]

Hi Everyone,

This is my first post here, so please excuse me if i happen to violate any of the communities's etiquettes.

So i am working with the below dataset:

Ad Name	Ad Revenue	CPRR	ER
X	200	500	23
Y	32	232	44
Z	339	443	442

Now, i want to display the top 10 Names based on a filter. The filter will contain the above three columns : Ad Revenue, CPRP, & ER.

Basically, the top 10 ad names will be displayed based on whataver filter selection i am chosing. PLease note the i have these as seperate columns in my current data.

Need urgent help with this. I am stuck to figure out on how to handle the dynamic user input and calcuate rank/top10 based on that.

Thanks,

Aman

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

The following is my sample for display top 10 based on Ad Revenue. You can reference to modify your report.

I have two tables. One is the sample you post. Another just contained one column of the column name from Table1. It will be used to a slicer.

1. Creat a measure

Measure 2 =
VAR se =  SELECTEDVALUE ( 'Table2'[Column1])
RETURN
 SWITCH( TRUE(),
 se = "Ad Revenue",
COUNTROWS(FILTER(ALL('Table1'),ISONORAFTER('Table1'[Ad Revenue],SELECTEDVALUE('Table1'[Ad Revenue]),DESC,'Table1'[Ad Name],SELECTEDVALUE('Table1'[Ad Name]),DESC))),
 se = "CPRR",
COUNTROWS(FILTER(ALL('Table1'),ISONORAFTER('Table1'[CPRR],SELECTEDVALUE('Table1'[CPRR]),DESC,'Table1'[CPRR],SELECTEDVALUE('Table1'[CPRR]),DESC))),
se = "ER",
COUNTROWS(FILTER(ALL('Table1'),ISONORAFTER('Table1'[ER],SELECTEDVALUE('Table1'[ER]),DESC,'Table1'[ER],SELECTEDVALUE('Table1'[ER]),DESC))),
BLANK()
)

1. Drag the measure to Visual level filters.

Filter type -- > change to Top N -- > show items : Top 10 -- > drag Rank to By value -- > Apply filter

Best Regards,

Xue Ding

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