Forum Discussion

pravkan's avatar
pravkan
Frequent Visitor
4 years ago

Filter Table Based on TOP N and Bottom N

I have 3 tables :  Pseudo Retailer Table  Bottom Sales Table  Bottom N Table   I want the Bottom Sales Table to be filter by table Pseudo Retailer on retailer name. Currently I have cre...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

pravkan ,Based on what I got so Far

You can try TOPN

 

CALCULATE([Total Sales],TOPN([SelectedBottomNValue_RetailerBottom],allselected('BOTTOM N RETAILER TABLE'[BOTTOM N RETAILER]),[Total Sales],DESC),VALUES('BOTTOM N RETAILER TABLE'[BOTTOM N RETAILER]))

 

use asc for bottom

 

 

pravkan's avatar
pravkan
Frequent Visitor
4 years ago

Hi amitchandak ,

 

How can I create a measure that will create a RANKX based on retailer name and product subcategory based on the measure you added as comment 

CALCULATE([Total Sales],TOPN([SelectedBottomNValue_RetailerBottom],allselected('BOTTOM N RETAILER TABLE'[BOTTOM N RETAILER]),[Total Sales],DESC),VALUES('BOTTOM N RETAILER TABLE'[BOTTOM N RETAILER]))

As I want to select TOP N or BOTTOM N retailer based on retailer name and product sub category.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    pravkan , You can use

     

    CALCULATE([Total Sales],TOPN([SelectedBottomNValue_RetailerBottom],allselected('BOTTOM N RETAILER TABLE'[RETAILER NAME], 'BOTTOM N RETAILER TABLE'[product subcategory NAME]),[Total Sales],DESC),VALUES('BOTTOM N RETAILER TABLE'[BOTTOM N RETAILER]))

     

    To filter we need use summarize of values

     

    Sumx(filter(addcolumns(Summarize('BOTTOM N RETAILER TABLE'

    'BOTTOM N RETAILER TABLE'[RETAILER NAME], 'BOTTOM N RETAILER TABLE'[product subcategory NAME]), "_1", [Rank], "_2", [Total Sales] ) , [_1] <= [SelectedBottomNValue_RetailerBottom]) , [_2])

     

    • pravkan's avatar
      pravkan
      Frequent Visitor
      4 years ago

      amitchandak 


      This is what I want to achieve with Power BI

       

       

      I don't know if this possible in dax.

       

      Normally I have all these data stored in a table called V_BOTTOM_SALES