Conditinally formated line and bar chart with LY and Target

[Martin-Prague2]

Hello expert comunity,

I am bit struggeling to find a right chart for the Conditionally formated LINE and BAR chart.

Requirements:

• Display an evolution of the KPI over the time.
• CY value should be conditinally formated based on the target achived / not achiever (binary logic)
• Display a target with the label
• Display a data labels for LY and CY values.

My current attempt in the Power BI:

Problems I am facing in Power BI:

• I have to split CY value for 2 measues GREEN and RED in orderd to be able to make conditinal formating. As I did this, there are now 3 measures on the axis insead 2. This is causing unnessesry spacing and issues with data labels.
• Lables for a target are repeted across the time period.

My desired result:

(without CY and LY labeling) this is just for your navigation.

I have also tryied KPI Column chart by MAQ, but it does not have a capabality with LY values.

Looking forward for your suggestions.

Martin

[Anonymous]

If you FORMAT your input data to have multiple dimensions based on color needed, you can then use either “Stacked Column Chart” or “Line & Stacked Column Chart”. The primary X axis dimension is Time, then add Secondary X-axis dimension (e.g. Type). Adding TYPE COLOR as the Legend will then produce the graph. Note I added additional Type (z) which is blank for cosmetic spacing. Note also included is a target line by using “Line & Stacked Column”.  see example below w/input data & visual settings

[v-juanli-msft]

Hi @Martin-Prague2

For the color problem, it is not possible to use conditional formatting for line and column chart.

I come up with some ideas to work around this problem, please let me know more about your data so that i can figure out.

Such as:

Is your data like this

Then the measure added to Value are like

%FO-CY = MAX([CY])/MAX([FO])

%FO-LY = MAX([LY])/MAX([FO])

Best Regards

Maggie

[v-juanli-msft]

Hi @Martin-Prague2

• Lables for a target are repeted across the time period.

For above requirement, here are two workarounds.

1.In line and column chart, select "Format"->"Data Labels"->"Customize series"->select "Target", then turn off "show" button.

   then add a text box with value "50%"

 2. change the visual to Clustered column chart, then add a constant line on it with value "50%".

Best Regards

Maggie