TOPN N (DAX Formulas) Issues

[souza.eq]

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?

 

[AlanSidman]

crossposted thread: https://www.excelforum.com/excel-fo...8-topn-n-dax-formulas-issues.html#post5885607

 

[vletm]

souza.eq​

As You should had been read ... twice

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

 

[AlanSidman]

and at least one more at: https://www.myonlinetraininghub.com/excel-forum/power-pivot/topn-n-dax-formulas-issues#p30223