Reply
Visitor
Posts: 1
Registered: ‎09-19-2018

Conditinally formated line and bar chart with LY and Target

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:

 

current.JPG

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.

 

desired result.JPG

 

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

 

 

 

 

 

 

Community Support Team
Posts: 2,773
Registered: ‎03-15-2018

Re: Conditinally formated line and bar chart with LY and Target

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%"
6.png

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

5.png

 

 

Best Regards

Maggie

Community Support Team
Posts: 2,773
Registered: ‎03-15-2018

Re: Conditinally formated line and bar chart with LY and Target

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

7.png

Then the measure added to Value are like

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

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

Best Regards

Maggie

 

Highlighted
Occasional Visitor
Posts: 1
Registered: ‎10-26-2017

Re: Conditinally formated line and bar chart with LY and Target

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

 

PowerBI_NCE_Chart.JPG