cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JoaoMS
Helper II
Helper II

Rank with repetead suppliers TopN + Others

Dear all, I have the following table:

 

TABLE 
SupplierNet Value
A3000
B1500
C2000
A1000
D2000
E4000
B1000
E500
F850

 

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

 

SupplierTOTAL
E4500
A4000
B2500
Others4850

 

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

 

TABLE  
SupplierNet ValueSupplier_Top3
A3000A
B1500B
C2000Others
A1000A
D2000Others
E4000E
B1000B
E500E
F850Others

 

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

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

mahoneypat
Super User
Super User

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")
 
Suppliers.png
If this works, please mark it as the solution.  Kudos are appreciated too.
Regards,
Pat
 




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

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

Regards

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!