Top N records + Other in Direct Query Mode

[rambo96]

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 that are taken into account are specified by the user through a date slicer. If I display this measure against the different categories that I have in one of the dimensions, it correctly shows the averages for each category (for the date range specified by the user).

Now I want to create a pie chart that shows the measure for the top 10 categories, while all other categories get summed in one last slice named "other". To do this I ranked the measure in descending order (using RANKX), and then created a new measure where all the products with rank > 10 get classified as 11. I had to do this using measures and not calculated columns because my model is in direct query mode and RANKX cannot be used in this scenario for calculated columns.
In the following screen an example of what I have achieved, sample data is shown:

From here I should simply group all the rows where "TOP10+OTHER" is equal to 11, and sum the "AVERAGE" for those rows, but this is where I get stuck, because I am trying to use a measure as a dimension when I try to aggregate, and this doesn't work.

Do anyone know how to solve this issue, or how to achieve the "TOP10+OTHER" visualization for my specific situation?

I thank everyone who will try to help me 🙂 

Cheers!

[v-yangliu-msft]

Hi  @rambo96 ,

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

[rambo96 · Accepted solution]

Hi @v-yangliu-msft 🙂

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