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

 

 

Input:

Product IDRevenue EURO
A100
A100
B50
C20
D10

 

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!

 

 

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    6 years ago
    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.
  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    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