Forum Discussion

Stuki's avatar
Stuki
Frequent Visitor
3 years ago

How to use Top n with additional filter

Hi,    Is there a way of pre-filtering a table before the Top n filter applies?   Scenario: I have a list of companies with sales data. I want to show the bottom n companies rated by Gross Margin...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

Stuki , assume sales is measure create a new measure use numeric parameter if need

 

GT 10k =sumx(Values(Customer[Customer]) , if([sales]>10000, [sales], blank()) )

 

then have topn sales measure 

 

sumx(keepfilterS(TOPN(5, ALLSELECTED(Customer[Customer]) , [GT 10k], asc)), [GT 10k])

 

TOPN with Numeric Parameter -https://youtu.be/cN8AO3_vmlY?t=26448

 

Learn Power BI: Dynamic TOPN using TOPN/Window and Numeric parameter: https://youtu.be/vm2mdEioQPQ

Stuki's avatar
Stuki
Frequent Visitor
3 years ago

Thanks amitchandak .

 

I'm going to need some time to fully understand this. I'm pretty new to Power BI so still on the learning curve when it comes to DAX. I have made an attempt to put this into my model and the GT 10k measure is working as it should (ie. returning a blank for any customer with sales < 10k). However, the 2nd measure doesn't return anything. This is what I have entered for it (I have changed the names of the measures so that are consistent with my naming convention):

 

Bottom n Revenue = sumx(keepfilterS(TOPN(10, ALLSELECTED(Customers[Company Name]), [Customer Revenue above $10k], ASC)), [Customer Revenue above $10k])
 
Is this correct? Also could you explain what this should do?
 
Please could you explain how I actually use these to produce a table of the bottom n Customers?
 
Thanks again