Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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?
Solved! Go to 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?
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'
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
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!
Nice! Thanks Sean.
My data is like this:
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:
@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.
@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.
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?
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! 👏
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.
This works with the Columns too by the way...