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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

RANKX Totals by category

Hello!, this is my first question for the PBI Community, I have tried many solutions posted in here but for some reason, none of them seems to work in my case, thanks in advance.

 

I need to have a calculated column that ranks de "Shipping_Line" by "Container_Volume" in order to add this as a filter so the DONUT CHART and table only shows the top 10 Shipping Lines for each Business_Unit.

 

 

Table name: Vol_TEUS 

 

Data:

 

Bussines_UnitShipping_LineMovement_TypeContainer_Volume
Company1MSKLoad21,337
Company1MSKLoad19,601
Company1MSKUnload19,505
Company1MSKUnload18,998
Company1MSKLoad17,978
Company1MSKUnload16,909
Company1MSKUnload16,871
Company1MSKUnload16,829
Company1MSKLoad16,532
Company1MSKUnload16,471
Company1MSKTranshipment16,331
Company1MSKUnload16,302
Company2APLUnload16,255
Company2APLLoad15,952
Company2MSCLoad15,767
Company3APLLoad14,268
Company3APLUnload14,253
Company3HLCLoad14,068

 

These are the formulas I have tried with no success:

 

 

 

RANKX(ALLEXCEPT(Vol_TEUS,Vol_TEUS[Shipping_Line]),CALCULATE(SUM(Vol_TEUS[Container_Volume])),,DESC,Dense)
RANKX(FILTER(Vol_TEUS,Vol_TEUS[Shipping_Line] = EARLIER(Vol_TEUS[Shipping_Line])),Vol_TEUS[Container_Volume],,ASC,Dense)

 

 

 

 

 

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png

 


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

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

For the data that you have shared, show the expected result in a simple Table.  From there, we can always build a pie chart.


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

@amitchandak Thank you, i have actually solved the "making a rank" issue.

 

@Ashish_Mathur Thank you!, find the example data below:

 

 

 

Actual Result Using = (RANKX(FILTER(Vol_TEUS,Vol_TEUS[Shipping_Line]=EARLIER(Vol_TEUS[Shipping_Line])),[Container_Volume],,ASC,Dense))

 

 

 

Desired Result = Rank by Container_Volume per Business_Unit

 

 

Bussines_UnitShipping_LineContainer_VolumeActual ResultDesired Result
Company2MSC5,87411
Company2COSCO5,90422
Company2ABC5,91233
Company2HSD5,92044
Company2EGR5,92555
Company2AHN6,02266
Company2HJN6,07277
Company2IUP6,15688
Company2ONE6,16899
Company2MSK6,2611010
Company3ONE7,663111
Company3HSD7,786122
Company3MSC7,881133
Company3ABC7,914144
Company3EGR8,215155
Company3AHN8,225166
Company3HJN8,313177
Company3IUP8,370188
Company3MSK8,410199
Company3COSCO8,4592010
Company1COSCO12,037211
Company1MSC12,085222
Company1ABC12,098233
Company1HSD12,704244
Company1EGR12,893255
Company1AHN13,010266
Company1HJN13,376277
Company1IUP13,624288
Company1ONE14,045299
Company1MSK14,0683010

 

I'd like to add that calculated column to the "page filters" in order to selec only the TOP 10 or TOP 5, which are different for each Business Unit.

Captura.JPG

 

 

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png

 


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

Thank you so much!!!! @Ashish_Mathur 

 

I am curently visitng your website!

 

You are welcome.

 


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

@Anonymous 

Try like

RANKX(ALL(Vol_TEUS,Vol_TEUS[Shipping_Line]),CALCULATE(SUM(Vol_TEUS[Container_Volume])),,DESC,Dense)

 

The best you refer to the first link. It has been explained in details

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

kentyler
Solution Sage
Solution Sage

Do you want the total container volume for each shipping line ? and then the top 10 totals ?





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Anonymous
Not applicable

Yes!

 

But the top 10 is different for each Business Unit.

 

Example: I have a "Chiclet slicer" visualization with the name of the companies, and I would like that to sync and show the top 10 per each company. 

 

I already have this measure:

 

Container_Volume total for Shpping Line = 
CALCULATE(
	SUM('Vol_TEUS'[Container_Volume]),
	ALLSELECTED('Vol_TEUS'[Shipping Line])
)

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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