Column Chart with colored target range

[flex99]

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.

[flex99]

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]

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]

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!

[littlemojopuppy]

They should show up when you drop the measure into the visualization.  Can you share a screen snip of what's happening?

[flex99]

I tried to attach a screenshot but it didn't work the last time. Here we go again - hope it works...

[littlemojopuppy]

They're measures so they're going to be reflected with the X axis as well...

[flex99]

Ok, so what would be needed to make the dotted lines go from side to side? Is there a way?

[littlemojopuppy]

Unfortunately I don't believe there is

[flex99]

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!

[littlemojopuppy]

You can use either the PERCENTILE or PERCENTILEX functions to create measures for the 75th and 85th percentiles.  From there just drop them into the visualization.

[amitchandak]

You can create a color measure and use that in conditional formatting(Advance Control), Choose a field and this measure

example

Color Category = SWITCH(true(),
Max(Sheet1[Category]) in {"A"} && Sheet1[Target]<10,"green",
Max(Sheet1[Category]) in {"B"} && Sheet1[Target]<10,"orange"
"red"
)

Refer : https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[littlemojopuppy]

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]

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