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
Anonymous
Not applicable

Sort 100% Stacked Column Chart by Percentage Values

In my Power BI Desktop file, I'm using the 100% Stacked Column Chart to compare 2 values for 10 people and the only options to sort the bars are:

  • x-axis value (person's name)
  • Value 1 of the bar chart
  • Value 2 of the bar chart

But, I want the bar charts to sort by the Value 1 % (which is Value 1 / [Value 1 + Value 2]) or the Value 2 %.  Thus, in the screenshot below, it would look like a waterfall with the blue colors sorting largest to smallest.

 

Is there a workaround for this?  Or something I'm not thinking of?

 

PBIDesktop_2016-05-02_08-37-11.png

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

Okay I got the %s you say in the Post above - and they calculate exactly as you say!

 

However we were there before with the calculated columns only - those were producing the same results?

And you said they were wrong?

Sort 100% Stacked Column Chart7.png

 

 

View solution in original post

23 REPLIES 23
KeesKuip
Frequent Visitor

A workaround (ugly) is to set the aggregation to 'Average' instead of 'sum' or 'count'.

You can then sort on 'Average of value1' or 'Average of value2'

Greg_Deckler
Super User
Super User

Add a calculated column with your % formula, go to the data model, select Value1 column, use the "Sort By" to change the sort column to your calculated column. Then, your Value1 should sort by your new column


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

That was what I initially tried to do (I should have mentioned that), but I found out the Sort By value doesn't allow you to select Measures.

Anonymous
Not applicable

Have you gotten it to work with a calculated column rather than a measure?

Anonymous
Not applicable

I'm inexperienced when it comes to DAX / Measures / Calculated Columns, but is it possible to write a Calculated Column to do what I need?  I thought it had to be a Measure since it's aggregating across multiple records?  Thanks for following up....

Sean
Community Champion
Community Champion

Can you provide a sample of what your data looks like? How its organized?

 

You CAN actually sort the 100% stacked column chart sort by Measres!

Sorted by Measure.png

Anonymous
Not applicable

Nice!  Thanks Sean.

 

My data is like this:

PBIDesktop_2016-05-02_15-51-52.png

Each Developer records their time by Category.  I created the following 2 Calculations:

 

Story Duration in Minutes = IF('Dev Time Tracking'[Category] = "Story", IF('Dev Time Tracking'[Start] >= 'Dev Time Tracking'[End], 0, DATEDIFF('Dev Time Tracking'[Start], 'Dev Time Tracking'[End], MINUTE)), 0)
Non-Story Duration in Minutes = IF('Dev Time Tracking'[Category] = "Story", 0, IF('Dev Time Tracking'[Start] >= 'Dev Time Tracking'[End], 0, DATEDIFF('Dev Time Tracking'[Start], 'Dev Time Tracking'[End], MINUTE)))

Is there a better way to write those Calculations?

 

Either way, my 100% Stacked Column Chart has:

  • Axis = Developer
  • Value
    • Non-Story Duration In Minutes
    • Story Duration In Minutes
Sean
Community Champion
Community Champion

@Anonymous

Create these 2 Measures

 

Story Durations = SUM('Dev Time Tracking'[Story Duration in Minutes])
Non-Story Durations = SUM('Dev Time Tracking'[Non-Story Duration in Minutes])

then create your chart as in the picture => placing these 2 Measures in the Values area

 

then sort... Let me know if it works!

Sort 100% Stacked Column Chart.png

Anonymous
Not applicable

@Sean Thanks so much for doing all this work here.

 

Unfortunately I did exactly what you suggested, but it's not working.  The Measures you gave result in a whole number, not a percentage, so it still sorts by that whole number.  So if someone has much larger numbers, they will appear first, regardless of their percentage.

 

PBIDesktop_2016-05-02_16-54-05.png

Sean
Community Champion
Community Champion

@Anonymous Wait a second now...

 

Turn on the Data Labels and tell me What do you see... Don't you see %

 

Sort 100% Stacked Column Chart3.png

 

More impotantly what's in the PAGE LEVEL Filter?

Anonymous
Not applicable

PBIDesktop_2016-05-02_17-15-25.png

 

There's nothing in the Page Level Filter.  The Report Level filter is irrelevant.  I removed it just to be sure and it has no effect.

 

%'s are displayed, but that's not what the data is.  Since it's the 100% Stacked, it will always show %'s.

Sean
Community Champion
Community Champion

@Anonymous Okay we we'll use the Duration in Minutes Column... here we go these are all Measures

 

You have to change the Table name and Duration Minutes column name a bit

 

Story Durations = CALCULATE(SUM(DevTimeTracking[Duration Minutes]), FILTER(DevTimeTracking, DevTimeTracking[Category]="Story"))
Non-Story Durations = CALCULATE(SUM(DevTimeTracking[Duration Minutes]), FILTER(DevTimeTracking, DevTimeTracking[Category]<>"Story"))
Total Story Durations = CALCULATE([Story Durations], FILTER(ALL(DevTimeTracking), DevTimeTracking[Category]="Story"))
Total Non-Story Durations = CALCULATE([Non-Story Durations], FILTER(ALL(DevTimeTracking), DevTimeTracking[Category]<>"Story"))
Story % = DIVIDE([Story Durations], [Total Story Durations], 0)
Non-Story % = DIVIDE([Non-Story Durations], [Total Non-Story Durations], 0)

 

This should work... Let me know

Sort 100% Stacked Column Chart4.png

 

 

 

Sean
Community Champion
Community Champion

@Anonymous Lets add the Totals... (if you look at the Table - I think we have all % now) Let me know...

 

Total Durations = [Story Durations] + [Non-Story Durations]
Total OVERALL Durations = [Total Story Durations]+[Total Non-Story Durations]
Overall % = DIVIDE([Total Durations], [Total OVERALL Durations], 0)

 

Now we have complete Stats

Sort 100% Stacked Column Chart5Data.png

 

 

 

Sean
Community Champion
Community Champion

@Anonymous Did you get it to work?

 

In a 100% stack chart everything has to add up to 100%!

 

Because we are comparing Story and Non-Story vs their respective Totals (apples and oranges from 2 different size pies)

 

the %s for each developer will not add up to 100% (so the chart makes the necessary adjustments to accomodate this)

 

Sort 100% Stacked Column Chart6.png

Anonymous
Not applicable

@Sean once again, thank you so much for dedicating so much time to this.  I've been very busy today and haven't been able to get back to it yet.

 

But, in a quick glance at what you've done, it looks like something is off.  

 

For each Developer, Story Durations + Non-Story Durations = Total Durations [for that Developer, not for everyone]

 

The formulas you have look like they're calculating the Story % on a basis of all Developers.

Thus in the example above...

Ben should have Story % of 480 / 480 = 100% and Non-Story % of 0%
Tim should have Story % of 120 / (120 + 210) = 36.4% and a Non-Story % of 63.6%

 

Make sense?

Sean
Community Champion
Community Champion

DELETED.

Sean
Community Champion
Community Champion

Okay I got the %s you say in the Post above - and they calculate exactly as you say!

 

However we were there before with the calculated columns only - those were producing the same results?

And you said they were wrong?

Sort 100% Stacked Column Chart7.png

 

 

Thank you very much for sharing, I reviewed all the forum conversation and it worked perfectly, the example is of 2 categories, Story and Non-Story in my case I had 4 categories (Pass, Fail, Blocked and Non-Execute), but I followed the example and it worked! 👏

Anonymous
Not applicable

This got it - thanks again @Sean!

 

It still seems overly complicated to do something that I'd imagine many people would want to do.  But, it also shows how much I need to learn basic DAX and how Measures work.

Sean
Community Champion
Community Champion

This works with the Columns too by the way...

Sort 100% Stacked Column Chart2.png

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.