Forum Discussion

pravkan's avatar
pravkan
Frequent Visitor
4 years ago

Filter Table Based on TOP N and Bottom N

I have 3 tables : 

  1. Pseudo Retailer Table 
  2. Bottom Sales Table 
  3. Bottom N Table

 

I want the Bottom Sales Table to be filter by table Pseudo Retailer on retailer name.

Currently I have created some measures 


Bottom Sales PSEUDO = IF([RankByMeasureValueBottom_PSEUDO]<=[SelectedBottomNValue_RetailerBottom],[Total Sales])

 

RankByMeasureValueBottom_PSEUDO = RANKX(ALL('Pseudo Retailer Table'[RETAILER_NAME]),[Total Sales],,ASC)

SelectedBottomNValue_RetailerBottom = IF(HASONEVALUE('BOTTOM N RETAILER TABLE'[BOTTOM N RETAILER]),VALUES('BOTTOM N RETAILER TABLE'[BOTTOM N RETAILER]),BLANK())
 
Total Sales = SUM(V_BOTTOM_SALES[TOTAL_SALES_AMOUNT])

 

Can you please help?

Need Help

4 Replies

  • 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])