Forum Discussion

ZakMeyer's avatar
ZakMeyer
Frequent Visitor
7 years ago

Dynamic Top N values with threshold slicer

I have a project where I am trying to dynamically show bottom 3 or bottom 5 changes in YoY sales based on a slicer selected. I am also trying too create a slicer that filters for a threshold number of products sold (100, 250, 500). I would like the ranking to follow my threshold slicer so each time I select a new one the bottom 3 or 5 is refreshed to show the largest decrease in sales with a certain number of products sold. 

 

Rank = IF(SUM(Table1[Products Sold]) > SELECTEDVALUE('Product Threshold'[Product Threshold]), RANKX(ALLSELECTED(Table1[Store Name]), SUM(Table1[YoY Sales Change]),,ASC),BLANK())

 

 

Not sure if this is possible since I just started using Power BI.

Need Help

2 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    7 years ago

    ZakMeyer,

     

    You may refer to the DAX below.

    Rank =
IF (
    SUM ( Table1[Products Sold] )
        >= SELECTEDVALUE ( 'Product Threshold'[Product Threshold] ),
    RANKX (
        FILTER (
            ALLSELECTED ( Table1[Store Name] ),
            CALCULATE (
                SUM ( Table1[Products Sold] )
                    >= SELECTEDVALUE ( 'Product Threshold'[Product Threshold] )
            )
        ),
        CALCULATE ( SUM ( Table1[YoY Sales Change] ) ),
        ,
        ASC
    ),
    BLANK ()
)
    • ZakMeyer's avatar
      ZakMeyer
      Frequent Visitor
      7 years ago

      Hi v-chuncz-msft

       

      You code worked to identify which stores are above the threshold of products sold but ranked them all as 1. 

       

      I also realized I left out some details and code that may be helpful.

       

      First is the stores are already regionally filtered so the view I showed is one region. Second is the code below.

       

      These two lines of code determine if value shows making the 3 or 5 with a 1 thenputting a visaul filter of "Should Store be Included is 1" on the table.

      SelectedTopNNumber = MIN('TopN'[Top])

Should Store Be Included = IF([Rank]<= [SelectedTopNNumber],1,0)

      This code is how YoY Sales change is calculated

      YoY Sales Change = DIVIDE(SUM(Table1[cur_Products Sold), SUM(Table1[Products Sold_12]),1)-1

      Hopefully that helps give some more context and thank you for you help so far.