Boxplots and Dumbells

Boxplot

_Box Plot = 
VAR MAXSales = MAX(financials[Gross Sales])
VAR MINSales = MIN(financials[Gross Sales])
VAR AVGSales = AVERAGE(financials[Gross Sales])
VAR Q1 = CALCULATE(PERCENTILEX.INC(financials, financials[Gross Sales], 0.25)) // alternate PERCENTILE.INC(financials[Gross Sales], 0.25)
VAR Q3 = CALCULATE(PERCENTILEX.INC(financials, financials[Gross Sales], 0.75)) // alternate PERCENTILE.INC(financials[Gross Sales], 0.75)
VAR IQR = Q3 - Q1
VAR AXISMAXRANGE = CALCULATE(MAX(financials[Gross Sales]), ALL(financials))
VAR AXISMINRANGE = CALCULATE(MIN(financials[Gross Sales]), ALL(financials))
VAR AXISRANGE = AXISMAXRANGE - AXISMINRANGE
VAR MAXNODE = MAXSales / AXISMAXRANGE * 100
VAR MINNODE = MINSales / AXISMAXRANGE * 100
VAR AVERAGENODE = AVGSales / AXISMAXRANGE * 100
VAR Q1NODE = Q1 / AXISMAXRANGE * 100
VAR Q3NODE = Q3 / AXISMAXRANGE * 100
VAR IQRNODE = IQR / AXISMAXRANGE * 100
RETURN
IF(
    HASONEVALUE(financials[Country]),
    "data:image/svg+xml;utf8," & "
    <svg width='102' height='20' xmlns='http://www.w3.org/2000/svg' xmlns:xlink='http://www.w3.org/1999/xlink' overflow='visible'>
        <rect id='box' x='" & Q1NODE & "' y='4' width='" & IQRNODE & "' height='12' fill='lightsteelblue'/>
        <line x1='" & MINNODE & "' y1='10' x2='" & Q1NODE & "' y2='10' stroke='black' stroke-width='2'/>
        <line x1='" & Q3NODE & "' y1='10' x2='" & MAXNODE & "' y2='10' stroke='black' stroke-width='2'/>
        <rect id='minmarker' x="&"'"&MINNODE&"'"&" y='1' width='2' height='20' fill='black'></rect>
        <rect id='maxmarker' x="&"'"&MAXNODE&"'"&" y='1' width='2' height='20' fill='black'></rect>
        <circle cx='" & AVERAGENODE & "' cy='10' r='3' fill='red'/>
    </svg>
    ", 
    BLANK()
)

Dumbell

_Dumbell Normalised = 
VAR Category_1 = MIN(financials[Gross Sales])
VAR Category_2 = MAX(financials[Gross Sales])
VAR AXISMAXRANGE = CALCULATE(MAX(financials[Gross Sales]),ALL(financials))
VAR AXISMINRANGE = CALCULATE(MIN(financials[Gross Sales]),ALL(financials))
VAR AXISRANGE = AXISMAXRANGE-AXISMINRANGE
VAR NODE_1 = Category_1/AXISMAXRANGE*100
VAR NODE_2 = Category_2/AXISMAXRANGE*100
VAR MINNODE = MIN(NODE_1,NODE_2)
VAR MAXNODE = MAX(NODE_1,NODE_2)
VAR Result = IF(HASONEVALUE(financials[Country]),
"data:image/svg+xml;utf8," & "
    <svg width='100' height='20' xmlns='http://www.w3.org/2000/svg' xmlns:xlink='http://www.w3.org/1999/xlink' viewBox='-4 0 108 20' overflow='visible'>
        <rect id='track' x="&"'"&MINNODE&"'"&" y='9' width="&"'"&MAXNODE&"'"&" height='2' fill='black'/>
        <circle id='cat_1' cx="&"'"&NODE_1&"'"&" cy='10' r='4' width='2' height='20' fill='orange'></circle>
        <circle id='cat_2' cx="&"'"&NODE_2&"'"&" cy='10' r='4' width='2' height='20' fill='blue'></circle>
    </svg>
", BLANK())

RETURN
Result

Leave a comment

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