cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mwimberger Frequent Visitor
Frequent Visitor

Creating an Excel "Up Down" Bar chart replica in Power BI

I've been struggling with this for a while and I am posting my first question to this forum hoping for brains trust assistance. 

 

I've been trying to replicate a "Up/Down" bar chart in Power BI showing the difference of a ratio from parity or 1. Essentially it is used to track ratios of two metrics ( A and B or A/B) to each other. If the ratio is 1, then A = B , if it is less than 1 ( say 0.8) then there could be say an overestimation of B the demoninator and if it is greater than 1, then A could be overestimated. 

 

An example of data could be as such.

 

StoreABRatio A : B
Alice Springs30412533.951.20
Brisbane87426723.951.30
Cairns10311085.010.95
Darwin826910208.090.81

 

This type of chart is relatively easy to plot in Excel by plotting two lines - one series is the ratio of A:B and the other is parity (1) , and then selecting Up/Down bars in the Design Menu, and then setting the two lines to "no line".  The key point is that the values "hang off" y=1.

 

 Up down bars.jpg

 

 

 

 

I know enough in Power BI to be dangerous but I can't seem to replicate this visualisation.  I am quite happy to do normal variance plots, but certain people HAVE to see the graph as in my screen shot ( labelled 3)

 

I have begun delving into using ggplot and custom R visuals and lots to pick up.

 

My question is has anyone tried this before in Power BI? Any pointers at all would be so very appreciated. 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Creating Up / Down Bars in visualisation

Hi @mwimberger

 

Here is a suggestion that I hope is close to what you want.

(sample pbix here)

It is a similar approach to the "old-fashioned" way of creating waterfall or Gantt charts in Excel and produces a visual like this:

image.png

 

  1. Assuming your table contains a column Ratio A : B already, create a series of measures:
    Average Ratio = 
    AVERAGE ( Data[Ratio A : B] ) //Arbitrary aggregation if each row is a data point
    
    Difference from 1 = 
    [Average Ratio] - 1
    
    Blank space = 
    MIN ( 1, [Average Ratio] )
    
    Difference from 1 Absolute (Pos) = 
    MAX ( [Difference from 1], 0 )
    
    Difference from 1 Absolute (Neg) = 
    - MIN ( [Difference from 1], 0 )

     

  2. Create a Line and Stacked Column Chart
    (or you could just use a Stacked Column Chart if you don't need data labels)
  3. Put Store on the Shared Axis
  4. Put Blank Space, Difference from 1 Absolute (Pos), and Difference from 1 Absolute (Neg) in Column Values
  5. Put Average Ratio in Line Values
    (this is just to allow data labels showing the ratio itself)
  6. Adjust Data colors so that Difference from 1 Absolute (Pos) and Difference from 1 Absolute (Neg) are appropriate colours, and Blank space is white (or other background colour)
  7. Change the line Stroke Width to zero
  8. Add data labels (if required) just for the Average Ratio series
  9. I also played with a report page tooltip, to ensure only the ratio is displayed in the tooltip (this could be tweaked)

One drawback is that the Blank space column values can't be transparent (at the moment anyway) so they have to be white (or a background colour), which means gridlines don't display nicely.

 

There could well be another custom visual out there that would b more suitable but I couldn't get any to work suitably (played with some of the Gantt & bullet visuals).

 

Regards,

Owen Smiley Happy



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




7 REPLIES 7
mwimberger Frequent Visitor
Frequent Visitor

Visualisation - Up / Down Bars hanging off y axis = 1

Hi there community! 

 

Been struggling with this for a whiile and I am posting my first question to this forum hoping for Brains Trust assistance

 

I've been trying to replicate a "Up/Down" bar chart in Power BI showing the difference of a ratio from parity or 1 (example below)

Up down bars 2.jpg

Essentially it is used to track ratios of two metrics ( A and B or A/B) to each other. If the ratio is 1, then A = B , if it is less than 1 ( say 0.8) then there could be say an overestimation of B the demoninator and if it is greater than 1, then A could be overestimated. 

 

An example of data could be as such.

 

StoreABRatio A : B
Alice Springs30412533.951.20
Brisbane87426723.951.30
Cairns10311085.010.95
Darwin826910208.090.81

 This type of chart is relatively easy to plot in Excel by plotting two lines - one series is the ratio of A:B and the other is parity (1) , and then selecting Up/Down bars in the Design Menu, and then setting the two lines to "No Line".  The key point is that the values "hang off" y=1.

 

 Up down bars.jpg

 

 

 

 

I know enough in Power BI to be dangerous but I can't seem to replicate this visualisation.  I am quite happy to do normal variance plots, but certain people HAVE to see the graph as in my screen shot ( labelled 3)

 

I have begun delving into using ggplot and custom R visuals and still  lots to pick up!

 

My question is has anyone tried making this visualisation before in Power BI? Any pointers at all would be hugely appreciated. 

 

Thanks in advance from sunny (weirdly) Melbourne Smiley Very Happy

mwimberger Frequent Visitor
Frequent Visitor

Creating Up / Down Bars in visualisation

Hi there community! 

 

Been struggling with this for a whiile and I am posting my first question to this forum hoping for Brains Trust assistance

 

I've been trying to replicate a "Up/Down" bar chart in Power BI showing the difference of a ratio from parity or 1 (example below)

Up down bars 2.jpg

Essentially it is used to track ratios of two metrics ( A and B or A/B) to each other. If the ratio is 1, then A = B , if it is less than 1 ( say 0.8) then there could be say an overestimation of B the demoninator and if it is greater than 1, then A could be overestimated. 

 

An example of data could be as such.

 

StoreABRatio A : B
Alice Springs30412533.951.20
Brisbane87426723.951.30
Cairns10311085.010.95
Darwin826910208.090.81

 This type of chart is relatively easy to plot in Excel by plotting two lines - one series is the ratio of A:B and the other is parity (1) , and then selecting Up/Down bars in the Design Menu, and then setting the two lines to "No Line".  The key point is that the values "hang off" y=1.

 

 Up down bars.jpg

 

 

 

 

I know enough in Power BI to be dangerous but I can't seem to replicate this visualisation.  I am quite happy to do normal variance plots, but certain people HAVE to see the graph as in my screen shot ( labelled 3)

 

I have begun delving into using ggplot and custom R visuals and still  lots to pick up!

 

My question is has anyone tried making this visualisation before in Power BI? Any pointers at all would be hugely appreciated. 

 

Thanks in advance from sunny (weirdly) Melbourne

Community Support Team
Community Support Team

Re: Creating an Excel "Up Down" Bar chart replica in Power BI

@mwimberger,

 

You may try adding the following calculated columns, then drag to Value and Color saturation.

Column =
Table1[Ratio A : B] - 1
Column 2 =
SIGN ( Table1[Column] )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Super User
Super User

Re: Creating Up / Down Bars in visualisation

Hi @mwimberger

 

Here is a suggestion that I hope is close to what you want.

(sample pbix here)

It is a similar approach to the "old-fashioned" way of creating waterfall or Gantt charts in Excel and produces a visual like this:

image.png

 

  1. Assuming your table contains a column Ratio A : B already, create a series of measures:
    Average Ratio = 
    AVERAGE ( Data[Ratio A : B] ) //Arbitrary aggregation if each row is a data point
    
    Difference from 1 = 
    [Average Ratio] - 1
    
    Blank space = 
    MIN ( 1, [Average Ratio] )
    
    Difference from 1 Absolute (Pos) = 
    MAX ( [Difference from 1], 0 )
    
    Difference from 1 Absolute (Neg) = 
    - MIN ( [Difference from 1], 0 )

     

  2. Create a Line and Stacked Column Chart
    (or you could just use a Stacked Column Chart if you don't need data labels)
  3. Put Store on the Shared Axis
  4. Put Blank Space, Difference from 1 Absolute (Pos), and Difference from 1 Absolute (Neg) in Column Values
  5. Put Average Ratio in Line Values
    (this is just to allow data labels showing the ratio itself)
  6. Adjust Data colors so that Difference from 1 Absolute (Pos) and Difference from 1 Absolute (Neg) are appropriate colours, and Blank space is white (or other background colour)
  7. Change the line Stroke Width to zero
  8. Add data labels (if required) just for the Average Ratio series
  9. I also played with a report page tooltip, to ensure only the ratio is displayed in the tooltip (this could be tweaked)

One drawback is that the Blank space column values can't be transparent (at the moment anyway) so they have to be white (or a background colour), which means gridlines don't display nicely.

 

There could well be another custom visual out there that would b more suitable but I couldn't get any to work suitably (played with some of the Gantt & bullet visuals).

 

Regards,

Owen Smiley Happy



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




mwimberger Frequent Visitor
Frequent Visitor

Re: Creating Up / Down Bars in visualisation

Owen, this is awesome.  I have a question in terms of the X Axis and this is my bad . I tried to give a simple explanation and used arbitary categories on the axis (Alice Springs etc) and assumed I could modify it from there.

 

If the axis was a date , or a date hierachy, would this still work? I tried to modify the measures and created another visualisation , but it falls over. Man Frustrated

 

Here is a variation of the table , with dates 

DateABRatio A : B
1/01/20181171420.82
1/02/20181081130.96
1/03/20181521141.33
1/04/20181371091.26
1/05/20181101230.89
1/06/20181391211.15
1/07/20181971331.48
1/08/20181151500.77
1/09/20181761081.63
1/10/20181321950.68
1/11/20181311171.12
1/12/20181821950.93
1/01/20191471850.79
1/02/20191601071.50
1/03/20191961511.30
1/04/20191391211.15

 

Thank you so much for your help so far!

 

Cheers

 

Manfred

mwimberger Frequent Visitor
Frequent Visitor

Re: Creating Up / Down Bars in visualisation

Hi Owen

 

Your help has been instrumental in solving this issue for me. Thank you!!

 

I managed to successfully adjust the visualization to deal with dates. 

 

Up down bars with dates.jpg

Here is a link for anyone that would like to use this as another example.

 

Cheers

 

Manfred

Super User
Super User

Re: Creating Up / Down Bars in visualisation

@mwimberger

Hi Manfred - looks like you solved the date version before I saw your reply - good work Smiley Happy

Glad I could help Smiley Happy

Cheers

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!