Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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??
Hi, @Anonymous
Any updates? I haven't got your reply. Is the problem solved?
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
@Anonymous is it kindly possible to provide the corresponding sample data and desire output?
@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/
Thanks Amit, I'll try this for sure
User | Count |
---|---|
40 | |
26 | |
22 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |