Forum Discussion

k-linerz's avatar
k-linerz
New Member
4 years ago
Solved

Dynamic Top N

Hello,
Ive got data in power BI pulled from SQL. Columns are : Date, SiteName, PersonName, SalesNumber, What I want to do is have a table to show the Date and then in that show the top 5 PersonName and the Sales Number. Each day will show the different peoples name and the volume. I need it to be dynamic so that If i then select a specific site in the SiteName slicer it will pull the top 5 from that specific site.
Example of outcome 


Ive searched around to find a way to do this but no luck. Any advice? 

Thanks

Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, k-linerz 

     

    You can try the following methods.

    Measure = 
Var _Top5=TOPN(5,ALLSELECTED('Table'[Name]),CALCULATE(SUM('Table'[Sales])),DESC)
Var _name=SELECTEDVALUE('Table'[Name])
Return 
IF(_name in _Top5,1,0)

    Put Measure in the Filter of the view and set it equal to 1.

    Please see the attachment for details.

     

    Best Regards,

    Community Support Team _Charlotte

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

2 Replies

  • v-zhangti's avatar
    v-zhangti
    Community Support
    4 years ago

    Hi, k-linerz 

     

    You can try the following methods.

    Measure = 
Var _Top5=TOPN(5,ALLSELECTED('Table'[Name]),CALCULATE(SUM('Table'[Sales])),DESC)
Var _name=SELECTEDVALUE('Table'[Name])
Return 
IF(_name in _Top5,1,0)

    Put Measure in the Filter of the view and set it equal to 1.

    Please see the attachment for details.

     

    Best Regards,

    Community Support Team _Charlotte

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