Forum Discussion

Junaid11's avatar
Junaid11
Helper V
4 years ago
Solved

Top N slicer

I have a table in which I have sales based on customer and countries. I created a measure for gross sales. I want ed to see this gross sales based on country by slicing top by Top 5, 10, 20 etc countries as shown in picture. I followed below video to get it. I want to filter not only this table visual but all the visuals like in the video.

https://www.youtube.com/watch?v=JUgROZURmrs&t=311s

I am not getting correct results.

I used below code:

Top Gross Sales = CALCULATE([Gross Sales Value],TOPN([Selected N],'Dummy Report',[Gross Sales Value],DESC,'Dummy Report'[Bill to Country ]))
 
When use slicer as Top 5 it still shows all the countries but there gross sales is changing. I want it to slice to show Top 5 countries wheb chosen Top 5 and similarly with Top 10.
 
I want to slice all of them below with same format:
Kindly let me know wheher to change code or what to do? It would be very helpful.
Thanks
Need Help

5 Replies

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    Junaid11 here is the measure, change value Top N with the slicer value to make it dynamic

     

    Top N Gross Sales = 
CALCULATE (
    [Gross Sales Value],
    KEEPFILTERS (
        TOPN (
            10,  --change this with the slicer selected value
            ALLSELECTED ( 'Dummy Report'[Bill to Country ] ),
            [Gross Sales Value],
            DESC
        )
    )
)

     

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

     

    Learn about conditional formatting at Microsoft Reactor

    My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

  • TomMartens's avatar
    TomMartens
    Super User
    4 years ago

    Hey Junaid11 , 

     

    you have to create.a measure like so:

     

    biil to country in TOPN =
var t = TOPN( ... , ...[bill to contry] )
return
if( max(...[bill to country] in t
, [your measure]
, blank()
)

     

    Hopefully, this helps to tackle your challenge. If not create a pbix file that contains sample data but still reflects your data model (tables, realtionships, calculated columns, measures), upload the pbix to onedrive or dropbox and share the link. If you are using Excel to create the sample data instead of the manual input method, share the xlsx as well. Do not forget to describe the expected outcome based on the sample data you provide.

     

    Regard,

    Tom

    • Junaid11's avatar
      Junaid11
      Helper V
      4 years ago

      Hello TomMartens ,

      I did not get the measure as there was some ... in it. I am attaching file. YOur response would be quite helpful.

      Thanks

      Pbix File 

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    4 years ago

    Top Gross Sales = CALCULATE([Gross Sales Value],TOPN([Selected N],'Dummy Report',[Gross Sales Value],DESC,'Dummy Report'[Bill to Country ]))

     

    try this.

     

    Top Gross Sales =
    CALCULATE (
        [Gross Sales Value],
        TOPN (
            [Selected N],
            ADDCOLUMNS (
                VALUES ( 'Dummy Report'[Bill to Country ] ),
                "@Gross Sales Value", [Gross Sales Value]
            ),
            [@Gross Sales Value], DESC
        )
    )

    • Junaid11's avatar
      Junaid11
      Helper V
      4 years ago

      Hello vanessafvg ,
      I have checked it. It is not filtering now. I am sending the file below so that you can directly have a look at it. 

      Pbix File 

      Thanks for responding.