Forum

TOPN N (DAX Formula...
 
Notifications
Clear all

TOPN N (DAX Formulas) Issues

2 Posts
2 Users
0 Reactions
139 Views
(@souza-eq)
Posts: 1
New Member
Topic starter
 

I want to have a column or a line with TOP N (lets say 3) products sold in a period, working along the data calendar hierarchy. Like, what were the top 3 products sold per year, quarter, month, day according pivot table data hierarchy.

Files and prints attached

Please refer to pivot table in tab Pivot Table (6) to check the measure I tried to use.

Problems Im facing:

1 I'v tried a bunch of top n tutorials and none of them work. Some use summarize, sumx, concatenatex… Question: I cant just use straight ahead TOP N like a function to have a column or line in the pivot table the 3 most sold products? I have to use some function to initialize the TOP N?

2 I did the relationship between tblProducts and tblOrders but the tblProducts does not show on the fields to select into the pivot table. Why?

3 =sumx(TOPN(3, SUMMARIZE(tblOrders, Calendar'[Date],topn sales,sum(tblOrders[Qty])),[topn sales],DESC),[topn sales])

tried this formula, but function sum [sum(tblOrders[Qty])] only accepts a table column and not another measure (used [Qty] so it could work).

Id like to use the top 3 from Total Revenue X, how can I do that?

Summarizing everything, how can I have the Top 3 products sold by period?

 
Posted : 28/10/2023 2:54 pm
Alan Sidman
(@alansidman)
Posts: 229
Member Moderator
Share: