Not consider duplicate IDs in a Top N

[Anonymous]

Hi all,

I´m trying to order a similar table in PBI, by sales desc. The thing is that I´d like to discard those companies with the same ID as it´s the case for Accesories and Accesories SL

I tried creating this column to replace Company column but it´s grouping the sales by ID and, that it´s not the expected result.

New_company= 

var a = CALCULATE( COUNT(Table[id]), FILTER(Table, Table[id] = EARLIER(Table[id])) )

return

IF(a >1,

        CALCULATE( MIN(SADB_Trends[partner_name]), FILTER(Table, Table[id]= EARLIER(Table[id])) ,

        Table[id] )

Many thanks in advance

[rocky09]

You have mentioned ID, where is the ID in you example?

[Anonymous]

True but it´s in the same table as the rest of the data. To make it easier just consider that if the sales quantity is the same, it´s means that they have the same ID 😅

[PC2790]

Hey @Anonymous ,

Can you show the current result and the expected result please?

[Anonymous]

Sure, this would be:

 I´d like to get for example, the min of company column in case the ID is duplicated

[rohit_singh · Accepted solution]

Hi @Anonymous ,

I have assumed that you have an ID column. Please try the following : 

1. Create a calculated column on your table to give you the min value of company for each ID.

Min Value =

CALCULATE(

MIN(CompSales[Company]),

ALLEXCEPT(CompSales, CompSales[ID])

)

2) Using ths table, create a calculated table that has company and sales

Min Sales =

SUMMARIZE(

CompSales,

CompSales[Min Value],

CompSales[Sales]

)

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊