Forum Discussion
TopN with duplicates
Hi guys, I would like to create a TOPN measure that sums up the revenues generated by the 2 most high-selling products. However, my table (a simplified version below) contains duplicates. Is it pos...
Please try this expression in a measure to get your desired result.
Top 2 Sum = VAR summary = ADDCOLUMNS ( VALUES ( 'Table'[Product ID] ), "@total", CALCULATE ( SUM ( 'Table'[Revenue EURO] ) ) ) RETURN SUMX ( TOPN ( 2, summary, [@total], DESC ), [@total] )If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
AllisonKennedy
Community Champion
Community ChampionAnonymous
What end goal are you looking for? There are built in TOPN filters on most visuals, so you could actually solve this by creating a Matrix visual with:
Product ID in Rows
Sum of Revenue in Values
Apply a filter on Product ID and set the type to TopN instead of Basic. Type 2 for the Top N. Drag Revenue into the the box to calculate the top 2 by. Click Apply filter.
You will now see a list of only the top 2 products by TOTAL revenue based on any other filters in your report.
Otherwise if you want to do this with DAX you could use a grouping function such as SUMMARIZECOLUMNS or other option to group by Product ID.
I hope I understood your question correctly.
What end goal are you looking for? There are built in TOPN filters on most visuals, so you could actually solve this by creating a Matrix visual with:
Product ID in Rows
Sum of Revenue in Values
Apply a filter on Product ID and set the type to TopN instead of Basic. Type 2 for the Top N. Drag Revenue into the the box to calculate the top 2 by. Click Apply filter.
You will now see a list of only the top 2 products by TOTAL revenue based on any other filters in your report.
Otherwise if you want to do this with DAX you could use a grouping function such as SUMMARIZECOLUMNS or other option to group by Product ID.
I hope I understood your question correctly.