Data Bars

data bars

Data Bars not centered (no subtotal)

Data Bars (positive_negative) = 
// Developed by Kerry Kolosko
VAR BAR_VALUE = SUM('Waterfall'[Value])  -- Summing the values for the current context
VAR MIN_ALL_VALUE = MINX(ALL('Waterfall'), 'Waterfall'[Value])  -- Getting the minimum value in the table
VAR MAX_ALL_VALUE = MAXX(ALL('Waterfall'), 'Waterfall'[Value])  -- Getting the maximum value in the table
VAR BAR_RANGE = MAX_ALL_VALUE - MIN_ALL_VALUE  -- Calculating the range of values
VAR X_SCALE = DIVIDE( 100, BAR_RANGE) -- Converting to percentage to scale
VAR X_ZERO = X_SCALE * ( 0 - MIN_ALL_VALUE )  -- Calculating the X position of the zero line
VAR BAR_START = IF(BAR_VALUE >= 0, X_ZERO, X_ZERO + (X_SCALE * BAR_VALUE))  -- Starting point of the bar
VAR BAR_WIDTH = ABS(X_SCALE * BAR_VALUE)  -- Width of the bar based on value
VAR PositiveColour = "blue"  -- Colour for positive values (replace with HEX values)
VAR NegativeColour = "orange"  -- Colour for negative values (replace with HEX values)
VAR BarColour = IF(BAR_VALUE >= 0, PositiveColour, NegativeColour)  -- Conditional colour
VAR AnimationEffect = "
  <animate attributeName='fill-opacity' from='1' to='0.5' dur='0.5s' repeatCount='3' />
"  -- Animation effect for the bars

RETURN
IF(HASONEVALUE('Waterfall'[Category 2]),  -- Checking if only one value is selected
"data:image/svg+xml;utf8," & 
"<svg width='100' height='20' viewBox='-2 -2 102 22' xmlns='http://www.w3.org/2000/svg' xmlns:xlink='http://www.w3.org/1999/xlink' display='block' overflow='visible'>
  <rect id='fill' x='" & BAR_START & "' y='3' rx='0' ry='0' width='" & BAR_WIDTH & "' height='15' fill='" & BarColour & "' fill-opacity='1'>
    " & AnimationEffect & "
  </rect>
  <rect id='x-zero' x='" & X_ZERO & "' y='0' rx='0' ry='0' width='1' height='20' fill='black' fill-opacity='1'/>
</svg>"
, BLANK())

Data Bars centered (no subtotal)

Data Bars (positive_negative_centred) = 
//Developed by Kerry Kolosko
VAR BAR_VALUE = SUM('Waterfall'[Value])  -- Summing the values for the current context
VAR MIN_ALL_VALUE = MINX(ALL('Waterfall'), 'Waterfall'[Value])  -- Getting the minimum value in the table
VAR MAX_ALL_VALUE = MAXX(ALL('Waterfall'), 'Waterfall'[Value])  -- Getting the maximum value in the table
VAR BAR_RANGE = MAX_ALL_VALUE - MIN_ALL_VALUE  -- Calculating the range of values
VAR X_ZERO = 50  -- Calculating the X position of the zero line
VAR X_SCALE = DIVIDE( 100, BAR_RANGE) -- Converting to percentage to scale
VAR BAR_START = IF(BAR_VALUE >= 0, X_ZERO, X_ZERO + DIVIDE(50 * BAR_VALUE, BAR_RANGE, 0))  -- Starting point of the bar
VAR BAR_WIDTH = ABS(DIVIDE(50 * BAR_VALUE, BAR_RANGE, 0))  -- Width of the bar based on value
VAR PositiveColour = "blue"  -- Colour for positive values (replace with HEX values)
VAR NegativeColour = "orange"  -- Colour for negative values (replace with HEX values)
VAR BarColour = IF(BAR_VALUE >= 0, PositiveColour, NegativeColour)  -- Conditional colour
VAR AnimationEffect = "
  <animate attributeName='fill-opacity' from='1' to='0.5' dur='0.5s' repeatCount='3' />
"  -- Animation effect for the bars

RETURN
IF(HASONEVALUE('Waterfall'[Category 2]),  -- Checking if only one value is selected
"data:image/svg+xml;utf8," & 
"<svg width='100' height='20' viewBox='-2 -2 102 22' xmlns='http://www.w3.org/2000/svg' xmlns:xlink='http://www.w3.org/1999/xlink' display='block' overflow='visible'>
  <rect id='fill' x='" & BAR_START & "' y='3' rx='0' ry='0' width='" & BAR_WIDTH & "' height='15' fill='" & BarColour & "' fill-opacity='1'>
    " & AnimationEffect & "
  </rect>
  <rect id='x-zero' x='" & X_ZERO & "' y='0' rx='0' ry='0' width='1' height='20' fill='black' fill-opacity='1'/>
</svg>"
, BLANK())

1 comment

Leave a comment

Your email address will not be published. Required fields are marked *