Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JoaoMS
Helper III
Helper III

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/
mahoneypat
Employee
Employee

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.