cancel
Showing results 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

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:

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
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
8 REPLIES 8
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
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_Unit Shipping_Line Container_Volume Actual Result Desired Result Company2 MSC 5,874 1 1 Company2 COSCO 5,904 2 2 Company2 ABC 5,912 3 3 Company2 HSD 5,920 4 4 Company2 EGR 5,925 5 5 Company2 AHN 6,022 6 6 Company2 HJN 6,072 7 7 Company2 IUP 6,156 8 8 Company2 ONE 6,168 9 9 Company2 MSK 6,261 10 10 Company3 ONE 7,663 11 1 Company3 HSD 7,786 12 2 Company3 MSC 7,881 13 3 Company3 ABC 7,914 14 4 Company3 EGR 8,215 15 5 Company3 AHN 8,225 16 6 Company3 HJN 8,313 17 7 Company3 IUP 8,370 18 8 Company3 MSK 8,410 19 9 Company3 COSCO 8,459 20 10 Company1 COSCO 12,037 21 1 Company1 MSC 12,085 22 2 Company1 ABC 12,098 23 3 Company1 HSD 12,704 24 4 Company1 EGR 12,893 25 5 Company1 AHN 13,010 26 6 Company1 HJN 13,376 27 7 Company1 IUP 13,624 28 8 Company1 ONE 14,045 29 9 Company1 MSK 14,068 30 10

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.

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

Thank you so much!!!! @Ashish_Mathur

I am curently visitng your website!

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
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

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.

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors