cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate IV
Advocate IV

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

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

22 REPLIES 22
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'

Super User IV
Super User IV

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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

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

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

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

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

@jasonwhurley

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

@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

@jasonwhurley 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?

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.

@jasonwhurley 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

 

 

 

Community Champion
Community Champion

@jasonwhurley 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

 

 

 

Community Champion
Community Champion

@jasonwhurley 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

@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?

DELETED.

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

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.

Community Champion
Community Champion

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

Sort 100% Stacked Column Chart2.png

Cool. I'm looking forward to hearing more.

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors