Top N slicer

[Junaid11]

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

[parry2k · Accepted solution]

@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.

[vanessafvg]

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]

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.

[TomMartens]

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]

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