Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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