Waterfall

Waterfall SVG = 
// Developed by Kerry Kolosko
VAR BAR_VALUE = SUM('Waterfall'[Value])  -- Size of change
VAR RUNNING_SUM = [Cumul. Value] -- Running total of changes
VAR SUBTOTALS = [Cumul. Value] -- If different calculcation required for subtotals

VAR MIN_ALL_VALUE = MINX(ALL('Waterfall'), [Cumul. Value])  -- Getting the minimum running total in the table
VAR MAX_ALL_VALUE = MAXX(ALL('Waterfall'), [Cumul. Value])  -- Getting the maximum running total in the table

VAR BAR_RANGE = MAX(MAX_ALL_VALUE,0) - MIN(MIN_ALL_VALUE,0)  -- Calculating the range of values
VAR X_SCALE = DIVIDE( 120, BAR_RANGE) -- Converting to percentage to scale

VAR IS_NEG = IF(BAR_VALUE>=0, 0, 1) -- If bar is negative then 1

VAR BAR_WIDTH = IF(HASONEVALUE(Waterfall[Category 2]), 
                        ABS(X_SCALE * BAR_VALUE),   
                        ABS(X_SCALE * SUBTOTALS)) -- Width of the bar based on value
VAR BAR_START = (X_SCALE * RUNNING_SUM) + (BAR_WIDTH*IS_NEG) - BAR_WIDTH  -- Starting point of the bar
VAR BAR_END = X_SCALE * RUNNING_SUM -- End point of the bar

VAR PositiveColour = "blue"  -- Colour for positive values (replace with HEX values)
VAR NegativeColour = "orange"  -- Colour for negative values (replace with HEX values)
VAR TotalColour = "black" -- Colour for totals (replace with HEX values)
VAR BarColour = IF(HASONEVALUE(Waterfall[Category 2]),
                    IF(BAR_VALUE >= 0, PositiveColour, NegativeColour),
                      TotalColour) -- Conditional colour

VAR Result = 

"data:image/svg+xml;utf8," & 
"<svg width='200' height='20' viewBox='-2 -2 202 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='1' rx='0' ry='0' width='" & BAR_WIDTH & "' height='18' fill='" & BarColour & "' fill-opacity='1'>
  </rect>
  <rect id='change' x='" & BAR_END & "' y='0' rx='0' ry='0' width='2' height='20' fill='grey' fill-opacity='1'/>
</svg>"

RETURN
Result

Leave a comment

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