Hitting a Roadblock

[m_wex]

Good evening. I am relatively new to PowerBi and have hit a roadblock and hoping for some help from the community. There is a table for the invoiced sales which is linked to a table containing the commodity, group, and sub-group fields. The link is through the part number which is unique.

I created a ranking table so I can display, using a checkbox selection on the page, to display sales results for the: Top5, Top 10, Top 25, and All Others. This was created using the following DAX.

Total Product Category by Rank =

VAR

RankingDimesion=Values('Product Category (2)'[Product Category] )

Var

RankingSelect = [Ranking Select]

Return

CALCULATE([Total Sales],

Filter( RankingDimesion,

RankX(all('Product Category (2)'[Product Category] ), [Total Sales], ,desc ) <= RankingSelect))

Supporting tables: 

Product Category (2)'[Product Category] – This is where product categories, groups, and sub-groups are stored. For each part number there is a category, group, and sub-group. 

Invoiced sales - This is where invoiced turnover by part number in stored. 

[Total Sales} – Is a basic calculation: Total Sales = sum(Sales[invoiced turnover])

The above DAX  works correctly, and sales results are ranked sales are filtered for the for the Product Category. The issue and what I need help with is that once the main selection (categories) is filtered, I wanted two supporting graphs for groups and sub-groups filtered based on the category results. If top 5 sales by category is selected, then only sales associated with the top 5 categories should be displayed for the for groups and sub-groups. Only the category is filtered by top 5,10, 25, etc. Each part number has only one category, group, and sub-groups.

Ultimately, all three graphs would have the same total sales but the proportion of the sales by the group and sub-groups will vary. Every attempt so far has failed in that either the final sales number does not match or the values in the group and sub-groups are not correct. 

Any clues or nudges in the right direction would be appreciated.

Any help with this would be greatly appreciated.

[amitchandak]

@m_wex , Power bi context sensitive. So If you use any thing other than category , then it will give top 5 inside that.

You can top 5/N category

Measure =

var _cat = TOPN(10,all('Product Category (2)'[Product Category] ),[Total Sales],DESC)

return

calculate([Total Sales], filter( 'Product Category (2)','Product Category (2)'[Product Category] in _cat))

This should give you only top category

refer this how sub category works - https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

[m_wex]

@amitchandak: Thank you for the quick response. Will give this a go. 

[m_wex]

Was not able to get the desired result with this. The main result (product category) is shown using a check box for top sales (5, 10, 25, all). If 5 is selected then only the top 5 categories sales are shown on a bar chart. What I am tring to do next is display another graph showing sales by sub-groups only for the category groups from the first graph. In my real world case for 5 catgories there would be 11 sub-groups. The results from one graph are used as a filter for a second graph.