Forum Discussion

flex99's avatar
flex99
Helper I
6 years ago

Column Chart with colored target range

Hi,

 

I was wondering if it's possible in Power BI to show a colored target range (like a green bar from 80-90%) within a column chart?

 

Any help will be appreciated!

Thanks in advance.

13 Replies

  • littlemojopuppy's avatar
    littlemojopuppy
    Community Champion

    Not specifically.  But there are a couple workarounds...

     

    This is the worst one because of the scale of the axis changes it could end up wrong.  But you could theoretically insert a box with the color over the chart.  Not recommended, but an option.

     

    The simplest one is to create measures for the upper and lower boundaries of the range and add them in as lines.

     

    if you want to get crazy, you could try an area chart or a stacked column chart  But this would involve having to create measures that build the actual value off the lowest boundary.  But you could set the area below the lowest boundary as completely transparent.  Could get very tricky to work successfully.

     

    Hope this helps!

    • littlemojopuppy's avatar
      littlemojopuppy
      Community Champion

      Here's an example from something I did...not exactly what you asked for but it gets the point across.

  • So when I get you both right there's no way to color a range in the graph, but only to color code the bars or to set min/max lines, correct?

     

    How would I exactly create measures for the upper (85%) and lower (75%) boundaries of the range and add them in as lines?

     

    Thanks for your help!

  • Sorry, I don't get it though I'd really love to know how you created the dotted lines in your above example.

     

    I'm not able to create a constant value of 75% with the Percentile function and "drop it into the visual" would also need some more words. When I use a combo chart and throw it in as 'line value' I will get a line between distinct data points but would rather like to have an independent line over the entire width of the chart.

     

    • littlemojopuppy's avatar
      littlemojopuppy
      Community Champion

      Here's code I used to calculate the 75th percentile...

      Observation Value 75th Percentile = 
          PERCENTILEX.EXC(
              FILTER(
                  ALL(Observations),
                  Observations[ObservationType] = SELECTEDVALUE(Observations[ObservationType])
              ),
              Observations[Value],
              .75
          )

       

      If what you're looking for if just a constant 75% and 85%, just create a measure like this

      Percent75 = .75
      
      Percent85 = .85

       

      To get the dotted lines, in the Formatting menu for the visualization, go to Shapes, Customize Series and then play with the settings

      • flex99's avatar
        flex99
        Helper I

        Thanks, that helped a lot! I was indeed merely looking for the ".075" measure 🙂

         

        One thing I couldn't manage is to get the dotted lines across the entire X axis (starting from the y axis and over to the very right). Is that somehow possible? It looked like on the screenshot you provided earlier!