Forum Discussion
Sort 100% Stacked Column Chart by Percentage Values
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?
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!
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.
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?
Anonymous Wait a second now...
Turn on the Data Labels and tell me What do you see... Don't you see %
More impotantly what's in the PAGE LEVEL Filter?
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.
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
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
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)
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.
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.
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! 👏