Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a stacked area chart where Reason is stacked by date. Right now the Legend for Reason is in alphabetical order. I want to apply a custom sort. I added a Sort_Dim table
Active | 1 |
NotAp1 | 2 |
NotHealthy | 3 |
Unknown | 4 |
Blocked | 5 |
ServiceDown | 6 |
NotInAd | 7 |
LargeQueue | 8 |
Seeding | 9 |
RecentPassive | 10 |
NodeDown | 11 |
And Power BI was able to autodetect the relationship. I then added a Sort_Order column to my Query1 table. In the Data window I then select the Reason Column in the Query1 table, then in the Modeling Tab I select Sort By Column and change it from Reason to Sort_Order. While that did change the order of the Reason in the chart, it also completly messed up the values of the chart (giving all of the Reasons values of 100). It changed it from:
T
to:
Why? Is there a way to apply a manual sort order?
Solved! Go to Solution.
Hey,
you have to use a numeric value to provide a certain order to a column "reason".
As you do use the reaon column from the table quey1 inside the visual, it's also necessary to sort the column 'query1'[reason] by the column 'query1'[Sort_order]
this will lead to the effect you mentioned ...
To resolve this, it is necessary to tweek your measure a little and also use the sort-column in the ALL(..., 'query1'[Sort_order]) function, like so:
Percent Of = SUM(Query1[percent])
/ CALCULATE(SUM(Query1[percent]), ALL(Query1[Reason],Query1[Sort_Order]))
This is described in more detail here:
https://www.sqlbi.com/articles/side-effects-in-dax-of-the-sort-by-column-setting/
Adjusting the measure leads to this area chart: it looks a little different then yours, but I'm not sure if this is what you are looking for, the stacked column chart looks as expected.
Hopefully this is what you are looking for.
Regards,
Tom
Hey,
please provide a screenshot from your relationship tab, that shows the relationships of your tables.
And also provide a screenshot that shows the used columns in the visual, mark the visual and check that the usage of the fields with the visual is visible.
Regards,
Tom
Here is the Relationship
And here is the usage fields:
Hey,
I'm wondering why you creaed the table "S(t)ort_Dim", because it is not used in combination with the visual
Can you please check how this behaves if you use the stacked column chart instead of the area chart, maybe it's an intrinsic behavior in the area chart.
Maybe you might consider to create a pbix with sample data, upload the pbix file to onedrive or dropbox and share the link.
Regards,
Tom
I created the Sort_Dim table because I've been reading that as a solution to the problem in other posts on community.powerbi.com, as a way to do a custom sort.
Changing the chart type from stack area to stacked column didn't do anything.
Here's a link: https://1drv.ms/u/s!Av_czEZXXEenhaIRQ2n527PiNEMKHg
Hey,
you have to use a numeric value to provide a certain order to a column "reason".
As you do use the reaon column from the table quey1 inside the visual, it's also necessary to sort the column 'query1'[reason] by the column 'query1'[Sort_order]
this will lead to the effect you mentioned ...
To resolve this, it is necessary to tweek your measure a little and also use the sort-column in the ALL(..., 'query1'[Sort_order]) function, like so:
Percent Of = SUM(Query1[percent])
/ CALCULATE(SUM(Query1[percent]), ALL(Query1[Reason],Query1[Sort_Order]))
This is described in more detail here:
https://www.sqlbi.com/articles/side-effects-in-dax-of-the-sort-by-column-setting/
Adjusting the measure leads to this area chart: it looks a little different then yours, but I'm not sure if this is what you are looking for, the stacked column chart looks as expected.
Hopefully this is what you are looking for.
Regards,
Tom
That is exactly what I am looking for. Thank you!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |