Dynamic Top N by Hierarchy

[sebastian_ebg]

Hi all,

I'm trying to create a visualisation to show the top N products sold (in a Treemap, hence wanting to reduce clutter), but I want to show this for each level of the hierarchy and not just restrict the entire hierarchy to the lowest level top N.

The model that I'm using is fairly simple:

The hierarchy is as follows:

-> Franchise

-> Platform

-> Name

I could use calculated columns and RANKX in the Products table, yes - but I don't think this would work because I also want to filter by time.

An example would be "What's the top 15 product Names sold in the top 15 Platforms in the top 15 Franchises in Fiscal Week 12?".

What I'd like to be able to do is show the top N Franchises, then when I drill down, show the top N Platforms within the Franchise, then when I drill down, show the top N products by Name within the Platform and Franchise, all the while respecting filtering within the report.

If I do a simple Top N filter on Name, it flows back upstream and affects the Franchise and Platform so what I'm after is a way to only filter downwards instead of in both directions.

Anyone got any ideas on how to solve this?

Alternatively, for a simpler solution, does anyone know of a way to filter only one level of a hierarchy, so that I can simply show Top N for the product level only, while still showing all Franchises and Platforms above it?

[keh3671]

Were you able to find a suitable solution for this report? I have the same issue.

Thank-you, 

[sebastian_ebg]

No, sorry - I didn't find a solution to this.

I just had to stick with top N overall filtering 😞

[v-caliao-msft]

@sebastian_ebg,

For your requirement, I don't think we can achieve it based on my understanding. 

Since you filter the data on the leave level, this filter also used for the whole hierarchy. If the filtered valued from the same Franchises, then only one Franchise will display on your chart.

Regards,

Charlie Liao