cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jasonwhurley Regular Visitor
Regular Visitor

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

Accepted Solutions
Super User
Super User

Re: 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?

Sort 100% Stacked Column Chart7.png

 

 

22 REPLIES 22
caseyh Member
Member

Re: Sort 100% Stacked Column Chart by Percentage Values

I think this would work:

 

Add a calculated column that contains the percentage value that you'd like to sort by. Call it "Sort_Pct" or something. Refresh the report and then go back to that sort menu in the top right of the column chart. The new Sort_Pct column should be an option there. 

 

(Sorry for lack of detail I haven't had time to test this and this is just from memory.)

Super User
Super User

Re: Sort 100% Stacked Column Chart by Percentage Values

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


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

Proud to be a Datanaut!


jasonwhurley Regular Visitor
Regular Visitor

Re: Sort 100% Stacked Column Chart by Percentage Values

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.

Super User
Super User

Re: Sort 100% Stacked Column Chart by Percentage Values

Ah, missed the part about it being a measure.


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

Proud to be a Datanaut!


caseyh Member
Member

Re: Sort 100% Stacked Column Chart by Percentage Values

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

jasonwhurley Regular Visitor
Regular Visitor

Re: Sort 100% Stacked Column Chart by Percentage Values

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

Super User
Super User

Re: Sort 100% Stacked Column Chart by Percentage Values

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

caseyh Member
Member

Re: Sort 100% Stacked Column Chart by Percentage Values

Cool. I'm looking forward to hearing more.

jasonwhurley Regular Visitor
Regular Visitor

Re: Sort 100% Stacked Column Chart by Percentage Values

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