Forum Discussion
Filter for Top 60% Sub-Category
I wish to have a filter such that
1. There is a table where there are columns containing Product names, Product Sub-Category names, Order Value USD values corresponding to each Product Sub-Category, Number of Suppliers values corresponding to each Product Sub-Category.
2. Now I want to filter Product Sub-Category column by "Sub-Total of SUM of Order Value USD column for each Product" such that the Filtered Product Sub-Category Column will show only Top 60% Product Sub-Categories by "Sub-Total of SUM of Order Value USD column for each Product".
Can someone help with the DAX Logic??
5 Replies
- amitchandak
Super User
Anonymous , try a measure like
Measure =
var _top = calculate(count(Table[Product Sub-Category]), filter(allselected(Table),Table[Product]= max(Table[Product])))
return
calculate(sum(Table1[SUMOrder Value USD]),filter(Table1, Table1[ID] in TOPN(_top, allselected(Table2[Product Sub-Category]),calculate(sum(Table1[SUMOrder Value USD])),DESC)) )or
Measure =
var _top = calculate(count(Table[Product Sub-Category]), filter(allselected(Table),Table[Product]= max(Table[Product])))
return
calculate(sum(Table1[SUMOrder Value USD]),filter(Table1, Table1[ID] in TOPN(_top, filter(summarize(allselected(Table),Table[Product], Table[Product Sub-Category]),Table[Product]= max(Table[Product]) ),calculate(sum(Table1[SUMOrder Value USD])),DESC)) )refer , here three can be replaced with _top
https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/
smpa01Community Champion
Anonymous is it kindly possible to provide the corresponding sample data and desire output?
- v-janeyg-msft
Community Support
Hi, Anonymous
I think your needs are not hard to achieve but I don't know what's 'Sub-Total of SUM of Order Value USD column'.
Are there multiple ‘Order Value USD’ values for one Product Sub-Category name?
Best Regards,
Community Support Team _ Janey - v-janeyg-msft
Hi, Anonymous
Any updates? I haven't got your reply. Is the problem solved?
