Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jader3rd
Employee
Employee

Sort stacked area chart which has Dates

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

Active1
NotAp12
NotHealthy3
Unknown4
Blocked5
ServiceDown6
NotInAd7
LargeQueue8
Seeding9
RecentPassive10
NodeDown11

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:

Before Applying SortBefore Applying SortT

to:

After Applying SortAfter Applying Sort

Why? Is there a way to apply a manual sort order?

 

1 ACCEPTED 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.

 

image.png

 

 

Hopefully this is what you are looking for.

 

Regards,

Tom

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

6 REPLIES 6
TomMartens
Super User
Super User

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 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Here is the Relationship

Table RelationshipTable Relationship

And here is the usage fields:

Active FieldsActive 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 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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.

 

image.png

 

 

Hopefully this is what you are looking for.

 

Regards,

Tom

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

That is exactly what I am looking for. Thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.