Forum Discussion
Problems with custom order on column chart
Hi All,
I have data at country level and I would like to add to the column chart summary column for region. I would like also to have a custom order on chart (not alphabetical). For this purpose I created separate table with all countries and region name - it includes two columns: country and numerical country rank. And now the fun begins. When I am sorting this table by country (alphabetically) the data for region is appearing on chart, but when I am sorting this table by country rank to have desired order at chart, the data for region disappears from the chart. Why is that? I am using DAX measure as below. How should I change it?
5 Replies
Hi maad300 - you can try the revised DAX approach that ensures that the region is handled properly regardless of how the table is sorted.
Region Measure =
VAR MeasureRegion =
CALCULATE(
[Measure],
REMOVEFILTERS(CountryRank[Country]), -- Removes any filters applied by the chart selection
ISBLANK(DATA[Country]) = FALSE -- Ensures only non-blank countries are considered
)
RETURN
SWITCH(
TRUE(),
SELECTEDVALUE(CountryRank[Country]) = "Region", MeasureRegion, -- If the region is selected, use the regional measure
[Measure] -- Otherwise, return the normal measure for the country
)your region should now appear correctly in the column chart, even when you have a custom sorting applied, and the data will remain intact.
Hope this works.
Hello rajendraongole1,
Many thanks for your fast response! I changed DAX formula according to your instructions but unfortunately problem did not disappear.
Best regards,
Hi maad300
Thanks for the reply from rajendraongole1 .
Because I don't have your data, I made a test about custom sorting for your reference. If it doesn't apply to your scenario, please provide some sample data so that we can better help you. How to provide sample data in the Power BI Forum - Microsoft Fabric Community Or show them as screenshots or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.
Index table:
1. Merge the Index table and the data table in Power Query.
2. Select the Index column of the Index table and expand the table
Close & Apply
3. Select the coutry column and sort by the index column
Output:
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Thanks for the answer! This sample is similar to mine, however the problem with sorting begins when I added to the Index table (in your case) the region name, let's call it TOTAL in this case.
What is important this TOTAL does not appear in 'financials' table, I am defining it in the separate measure. And when the Index table is sorted by country, the value for TOTAL appears on chart, but when I want to sort index table by index, the value for TOTAL disappears on the chart. I completely don't understand why it is happening.Best regards,
Hi maad300
After my test, I did not reproduce your problem. Could you please tell me how to write the TOTAL measure? What is its logic? It would be better if you can share pbix. Please remove any sensitive data in advance.
Best Regards,
Yulia Xu
