Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 :
Solved! Go to 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
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
Hey @menphis21 ,
please adjust the sharing settings of the pbix on google drive. Currently, a login is required.
Regards,
Tom
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
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
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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |