Rank with repetead suppliers TopN + Others

[JoaoMS]

Dear all, I have the following table:

TABLE
Supplier	Net Value
A	3000
B	1500
C	2000
A	1000
D	2000
E	4000
B	1000
E	500
F	850

If I create a Table with a Top3 Rank + Others, it will look like this:

Supplier	TOTAL
E	4500
A	4000
B	2500
Others	4850

But I just want to create a Calculated Colum (DAX) to show like this:

TABLE
Supplier	Net Value	Supplier_Top3
A	3000	A
B	1500	B
C	2000	Others
A	1000	A
D	2000	Others
E	4000	E
B	1000	B
E	500	E
F	850	Others

I found some solutions but just when the Supplier appears once. In this case, the same supplier can appear many times in the database.

Thanks in advance,

Joao

[Ashish_Mathur · Accepted solution]

Hi,

Write these calculated column formulas

Value of supplier = CALCULATE(SUM(Data[Net Value]),FILTER(Data,Data[Supplier]=EARLIER(Data[Supplier])))

Rank = if(RANKX(ALL(Data),Data[Value of supplier],,,dense)<=3,Data[Supplier],"Others")

Hope this helps.

[mahoneypat]

I know there is a more efficient way to write this expression, but it works for what you are looking for I believe.  FYI that in your sample data, Supplier B was marked both as B and Others.  If that was correct, I misunderstood your request.  This expression returns B for all B rows.

New_Top3 = var currentsupplier = Suppliers[Supplier]

var totalthissupplier = CALCULATE([TotalNetValue], ALL(Suppliers), Suppliers[Supplier]=currentsupplier)

var filteredtable = FILTER(ALL(Suppliers[Supplier]), CALCULATE([TotalNetValue], ALL(Suppliers), Suppliers[Supplier]=EARLIER(Suppliers[Supplier]))>=totalthissupplier)

return if(COUNTROWS(filteredtable)<=3, currentsupplier,"Others")

If this works, please mark it as the solution.  Kudos are appreciated too.

Regards,

Pat

[JoaoMS]

Hi, thank you for the quick response, I edited the post regarding "Supplier B".

About the proposal, I do not have this table "[TotalNetValue]" so I couldn't solve this issue. We just have one table with the two columns "Suppliers" and "Net Value".

Regards,

Joao

[mahoneypat]

Sorry I didn't clarify that. The TotalNetValue is just a measure that is sum of the Net Value column. 

Regards

Pat