1 Reply Latest reply on Jun 3, 2015 4:39 PM by Sarah Slattery

    Stacked Bar chart shows total, but used filter to populate segments of the stacked bar chart

    Brian Mandel

      I have a workbook of a stacked bar chart with measure values in the rows and where the bar chart is broken out by ethnicity. I need a stacked bar chart that shows the total counts, but will allow the user to select races which would then populate the bar chart. Basically, need the stacked bar chart one color, but when the different races are selected the counts add up and the bar chart is stacked with different colors. see attached workbook.

        • 1. Re: Stacked Bar chart shows total, but used filter to populate segments of the stacked bar chart
          Sarah Slattery

          Hi Brian,

           

          If I interpreted your request correctly, then I have a solution for you to try that uses a combination of reference lines, a dual axis, and the lookup function.

           

          • First create two new fields:
            • Ethnicity Lookup Filter: lookup(min(Ethnicity),0)
            • Population Total: total(sum(Population))
          • Drag Ethnicity Lookup Filter to the Filters shelf
            • Left click and set Compute Using to Year
            • Select Show Quick Filter
          • Drag Population Total to the right of the Population measure in the Rows column
          • Right click the y-axis of Population Total and select Dual Axis
          • Right click the secondary y-axis and make the following selections:
            • Select Synchronize Axis
            • Select Move Marks to From
            • Uncheck Show Header
          • Right click the primary y-axis and Add a Reference Line
            • Select Line
            • Set the Scope to Per Cell
            • Set Value to sum(Population) and select Total from the drop down
            • Set Label to None
            • Set Fill Below to a grey color

           

           

          Hope that helps,

          Sarah