Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 5
v-janeyg-msft
Community Support
Community Support

Hi, @Anonymous 

 

Any updates? I haven't got your reply. Is the problem solved?

v-janeyg-msft
Community Support
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

smpa01
Super User
Super User

@Anonymous  is it kindly possible to provide the corresponding sample data and desire output?

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
amitchandak
Super User
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/

Anonymous
Not applicable

Thanks Amit, I'll try this for sure

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors