Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Top N records + Other in Direct Query Mode

Hi everyone,   I've been stuck on this issue for days now, and I would really appreciate if anyone can help me.   I have a measure that calculates an average over a number of days, where the days...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ðŸ™‚

     

    Thank you a lot for taking the time to examine my problem. I tried to implement your solution in my project, but unfortunately it didn't work out. Maybe it's because the project on which I'm working is really complex and a lot of variables come into place, I don't know.

    Anyway, I finally managed to solve my issue by following the steps in this video: https://www.youtube.com/watch?v=yGFcCbXn_g0

    I hope this, together with your answer can help others facing the same situation. Thank you again.

     

    Best Regards,

    rambo96

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi  Anonymous ,

 

Here are the steps you can follow：

1. Create a table with Enter data.

2. Create measure.

Measure =
SWITCH(
    TRUE(),
    MAX('Table2'[categories]) =1,SUMX(FILTER(ALL('Table'),[Rank]=1),[avg]),
MAX('Table2'[categories]) =2,SUMX(FILTER(ALL('Table'),[Rank]=2),[avg]),
MAX('Table2'[categories]) =3,SUMX(FILTER(ALL('Table'),[Rank]=3),[avg]),
MAX('Table2'[categories]) =4,SUMX(FILTER(ALL('Table'),[Rank]=4),[avg]),
MAX('Table2'[categories]) =5,SUMX(FILTER(ALL('Table'),[Rank]=5),[avg]),
MAX('Table2'[categories]) =6,SUMX(FILTER(ALL('Table'),[Rank]=6),[avg]),
MAX('Table2'[categories]) =7,SUMX(FILTER(ALL('Table'),[Rank]=7),[avg]),
MAX('Table2'[categories]) =8,SUMX(FILTER(ALL('Table'),[Rank]=8),[avg]),
MAX('Table2'[categories]) =9,SUMX(FILTER(ALL('Table'),[Rank]=9),[avg]),
MAX('Table2'[categories]) =10,SUMX(FILTER(ALL('Table'),[Rank]=10),[avg]),
MAX('Table2'[categories]) =11,SUMX(FILTER(ALL('Table'),[Rank]>10),[avg]))

3. Result:

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ðŸ™‚

     

    Thank you a lot for taking the time to examine my problem. I tried to implement your solution in my project, but unfortunately it didn't work out. Maybe it's because the project on which I'm working is really complex and a lot of variables come into place, I don't know.

    Anyway, I finally managed to solve my issue by following the steps in this video: https://www.youtube.com/watch?v=yGFcCbXn_g0

    I hope this, together with your answer can help others facing the same situation. Thank you again.

     

    Best Regards,

    rambo96