Sorting 100% Stacked Bar by category

gbuckley
gbuckley Member
in Charting

I have a 100% Stacked Bar Chart for net hours worked. Each bar is an employee and is broken into net hours by pay category (OT1.5 vs. OT2.0 vs. Reg).

 

I want to sort the chart so that the employee with the lowest percentage of OT1.5 is on top and the employee with the largest percentage is on bottom.

 

I started by fiddling with the "Sort on Totals" toggle under "General" in the Chart Properties pane. However, this sorts the bars by total net hours worked in all categories, not by percentage in one category.

 

So I made a beast mode (see attached photo). I checked to make sure that the beast mode calculates correctly - it does (see attached photo of single bar chart with "Sort Order" as the values field. It correctly returns the percentage of OT1.5 hours for each employee). But when I use the "Sort Order" beast mode to sort the 100% stacked bar, it does not work (see photo of 100% stacked bar with "Sort Order" in the sorting pane. The bars are not sorted correctly). It looks like the issue is that when I use the beast mode to sort, Domo calculates the beast mode for each row of the dataset instead of as an aggregate (see photo of data table. "Sort Order" returns 1 for OT1.5 rows and 0 otherwise).

 

Any pointers on how to adjust the beast mode so that the bars sort correctly is much appreciated!        

 

*All data is fictional and used only to illustrate this idea

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    @rgbuckley 

     

    Because how the 100% stacked bar works and expects a single column for a series it's not quite possible. However! You can hack around it with a normal stacked bar and then plot the percentages.

     

    Screen Shot 2020-03-12 at 3.52.26 PM.png

     

    You'll need to make distinct beast modes for each type of Pay Cat Name using the same formula you posted earlier, just changing the value you're comparing against.

    SUM(
      CASE
      	WHEN `Pay Cat Name` = 'OT1.5'
      	THEN `Net Hours`
      	ELSE 0
      END
    )
    /
    SUM(`Net Hours`)

    Reg, OT1.5 and OT2 etc.

     

    You can then sort based on the OT1.5 beast mode ascending which will bubble the records with the lowest OT1.5 percentage at the top.

     

    One caveat to this approach is that you do lose the total amount of hours per type.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • Godzilla
    Godzilla Contributor

    this is a tough one. Interested to see if anyone else has a solution to this. 

    Domo Arigato!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • GrantSmith
    GrantSmith Coach
    Answer ✓

    @rgbuckley 

     

    Because how the 100% stacked bar works and expects a single column for a series it's not quite possible. However! You can hack around it with a normal stacked bar and then plot the percentages.

     

    Screen Shot 2020-03-12 at 3.52.26 PM.png

     

    You'll need to make distinct beast modes for each type of Pay Cat Name using the same formula you posted earlier, just changing the value you're comparing against.

    SUM(
      CASE
      	WHEN `Pay Cat Name` = 'OT1.5'
      	THEN `Net Hours`
      	ELSE 0
      END
    )
    /
    SUM(`Net Hours`)

    Reg, OT1.5 and OT2 etc.

     

    You can then sort based on the OT1.5 beast mode ascending which will bubble the records with the lowest OT1.5 percentage at the top.

     

    One caveat to this approach is that you do lose the total amount of hours per type.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
This discussion has been closed.

Categories