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 o...
Need Help
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.