TopN with duplicates

[Anonymous]

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 possible to create a TOPN measure for revenue (column 3) that recognizes duplicates by product ID (column 1)?

Input:

Product ID	Revenue EURO
A	100
A	100
B	50
C	20
D	10

My desired output would be a TOPN measure that gives the result 250 for the top 2 most high selling products, in this instance: A generates 200 EURO and B 50 EURO.

So the measure should on the one hand recognize duplicates (here: A) but still summarize the revenue for duplicate products.

Hope that is not too confusing!

[Anonymous]

Hi @Anonymous ,

According to my understanding, you want to sum the top2 values when there are duplicate values, right?

You could use the following formula:

rank =
RANKX (
    ALLSELECTED ( 'Table' ),
    CALCULATE ( SUM ( 'Table'[sumColumn] ) ),
    ,
    DESC,
    DENSE
)

sumTop2 =
CALCULATE (
    SUM ( 'Table'[Revenue EURO] ),
    FILTER ( ALL ( 'Table' ), [rank] <= 2 )
)

My visualization looks like this:

Is the result what you want? If not, please upload some data samples and expected output.

Please do mask sensitive data before uploading.

Best Regards,

Eyelyn Qin

[mahoneypat · Accepted solution]

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]

@Anonymous
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.

[Anonymous]

Hi @Anonymous 

Create a measure as

Measure = CALCULATE(SUMX(TOPN(2,VALUES('Table'[Revenue]),'Table'[Revenue],DESC),'Table'[Revenue]))

 Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Regards,
Pranit