Ranking but make it not change by adding a value in the legend of a graph

[menphis21]

Hi,

I am ranking by companies the amount from a table. I have also in the same table each departement concerned by the client.

Client | DEP | Amount

Client A |  Dept A |  5000

Client A | Dept B |  300

Client B |  Dept A | 4900

Client C | Dept C | 4600

Client C | Dept A | 200

I made a rank like that :

RANKX(ALL('Collection'[Client),

                                           CALCULATE(Collection[Amount]),,desc,Dense)
and it works, i obtain:

Client A | 5300 | 1 

Client B | 4900 | 2

Client C | 4800 | 3
But i would like to add on my graph the repartiton by the DEP without changing my ranking based on my client.

How can i do ?

Thank you,

Avy

[TomMartens]

Hey @menphis21 ,

okay, now I understand what you are trying to achieve, so you don't have to adjust the settings for the pbix file on google drive (at least not because of me).

By default the legend is ordered by characters and not by value (you already mentioned this). No matter what you try to do, you can't order the items of the legend by value dynamically. This is only possible if you use another column that contains the values determining the sort order: Sort one column by another column in Power BI - Power BI | Microsoft Learn
But you have to be aware that the ordering of the legend items (the departments) using another column is not dynamic, meaning reflecting the current filtering.

You have to use custom visuals if you need a more dynamic solution. The below screenshot shows the Inforiver Charts visual on the right.

As you can see, the legend reflects the order of the segments in the stack (the single bar). From my point of view, the Inforiver Charts visual is the most powerful custom visual without the need to write any code (considering common data visualizations). More is only possible if you are using one of my favorite custom visuals: Deneb (Declarative Visualization in Power BI | Deneb (deneb-viz.github.io))
Nevertheless, ordering the items of the legend (representing the segments of the stack) can be difficult if there are many stacks because there is just one legend. So you have to decide if the ordering is determined by the largest stack, no matter the segments or if the ordering will be determined by the largest segment across all stacks.

Hopefully, this provides some ideas on how to tackle your challenge.

Regards,

Tom

[menphis21]

Thank you @TomMartens  🙂

[TomMartens]

Hey @menphis21 ,

please adjust the sharing settings of the pbix on google drive. Currently, a login is required.

Regards,

Tom 

[TomMartens · Accepted solution]

Hey @menphis21 ,

if you change your ranking measure to this:

Ranking = 
 RANKX(
    ALL( 'Table'[Client] )
    , CALCULATE( SUM( 'Table'[Amount] ) , ALL( 'Table'[DEP] ) )
    ,
    , desc 
    , Dense
)

You can create a table visual like this:

The first parameter of the RANX function is a table containing all the values that have to be ranked.
Using ALL( ...Client) removes the filter context on the client column, which is applied by the row of the table visual. When you add the DEP to the table visual,  column another column contributes to the filter context. Using ALL( ...DEP) inside the CALCULATE is blocking the application of this column to the filter context. This means the DEP column is ignored during the calculation of the RANK.

Hopefully, this provides what you are looking for.

Regards,

Tom

[menphis21]

Thank you @TomMartens , it works 🙂 ( with the field Dpt inside my table)
But i have created a table with my department link to my collection table just to define the order of my departement 

Dep A |  1

Dep B | 2

etc

And seems that depending the selection, its not giving me the same results

[TomMartens]

Hey @menphis21 ,

I assume this will not work, the reason for this is that there are same departments for different clients like department A (Client 1, 2, and 3).

Consider creating a pbix file that contains sample data, but still reflects your data model (tables, relationships, calculated columns, and measures). Upload the pbix file to onedrive, dropbox, or google drive, and share the link.

Please describe the expected result based on the sample data you provide.

Regards,

Tom

[menphis21]

Hey @TomMartens ,

Its really the same, just the field of departement is on another table to order it. Probably i made it wrong or there is another way to do it.

Here's the example : https://fromsmash.com/WQt80I~2rZ-dt

Thank you for your help

[TomMartens]

Hey @menphis21 ,

thanks for preparing the pbix, but for the security reasons I will download files only from the data stores I mentioned above.

Nevertheless, I do not understand how you want to order the departments.

Based on the clients department A is either on rank 1 (client 1) or on rank 3 (client 3). Can you elaborate on the ordering of the departments.

Regards,

Tom

[menphis21]

Hi @TomMartens ,

thank you for your return. Sorry i didnt pay attention, you will find below a new link on google drive.

About the order of the departement, its only to permit me to display in the order on the graph.

Without this table, it will be ordered by letters (attached screenshot to check the difference)

(for example i want that Audit department need to be first on the list)

https://drive.google.com/file/d/12b-PaVPQQQkAJEKOd30-TbIMp-iqSRBH/view?usp=share_link