Forum Discussion
Top 10 Rank based on user filter selection
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
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.
- 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() )- 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.
1 Reply
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.
- 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() )- 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.