Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
@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/
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
71 | |
36 | |
21 | |
19 | |
15 |
User | Count |
---|---|
125 | |
37 | |
29 | |
29 | |
24 |