Filtered tables and rank

[RobertaCC]

Hi everybody!

I'm actually new and I really need a hand as far as I'm stuck over this matter.

I start from a highly granular database and I need to aggregate the products by their ranking calculated on the related sales. So, to be clear, let's say that I have 100 products and I need to visualize the firse 7 products by the amount of sales and all the other 93 products have to be aggregated (and showed in the istograms) into the category "other". The ranking should be then used as a category in an istogram. What's more, the ranking should be calculated every time on the filters applied by the user (that is, time and product-related dimensions).

I tried by building a table derived from the original database (using the function summarizecolumns, to be clear) so that to estrapolate just the list of products and the related sales but:

- I'm not able to make the filters function: I mean, the filters applied on the original database don't affect the new table (I tried the function treatas, but I couldn't make it work)

- and last, even though I had made it, what I'd need next is to relate the ranking to a time-trend (so, let's say that I want to see, for a given period, the weekly sales distincted for the first 7 product and, aggregated, for all the "other" products

Is that possible?

[RobertaCC]

First of all, thank you very much! The result in your power bi is what I'm looking for!!

But, unfortunately, the measure doesn't work in my powerbi. What the measure returns is partial: it returns only the first 2 or 3 products (and not the actual first 2 or 3 products, but others) and it doesn't display at all the category "others". Could it be related to the granularity of the database?

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file whether it suits your requirement.