Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Top N filter issue

Hi,

i have problems with Top N filter. I have simple Weeknum column created to show me week in which record was created. I want to filter last 8 weeks but when i apply Top N filter i get incorect values.

 

exemple: i have weeks from 5 to 20 formated as numbers and after aplying filter for the last 8 weeks i get [5,6,7,15,17,18,20] instead of [13,14,15,16,17,18,19,].

 

Help me with this, please tnx

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous ,

    Please check my blog for this

    https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

    I used week rank for that so that it can work across years

    Last 8 period Sales =
    Var _min = maxx(allselected('Date','Date'[period Rank]) -8
    Var _max = maxx(allselected('Date','Date'[period Rank])
    CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=_min && 'Date'[Week Rank]<=_max))

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    hi  Anonymous 

    Please set as below for topn setting:

     

     

    Please make sure By value as above.

     

    Regards,

    Lin

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi,

       

      it helped i my cse when i choose average of week.Tnk

      Ii didn't know that you can change sum od average value in top n filter.

       

      Tnx

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      How the changing values from average to sum or some other calculation changes the values we are getting?
      I was getting different values with sum but with the column set to average in the filter gives correct values. Can you explain?