Forum Discussion

wujunmin's avatar
wujunmin
Icon for Advocate III rankAdvocate III
5 years ago

Sharing: Creat your own visuals with DAX+SVG

Mini Charts for Table and Matrix 1.bar     Bar = VAR W1=MAXX(ALL('table'),[data])/100 VAR W2=[data]/W1 VAR Color=IF([data]<100,"#B7472A","#217346") RETURN "data&colon;image/svg+xml;utf8,"...
chart
SVG
wujunmin's avatar
wujunmin
Icon for Advocate III rankAdvocate III
5 years ago

Dumbbell Chart

Green: this year

Orange: last year

Black line: up

Gray line: down

 

Dumbbell = 
VAR ItemCount =
    DISTINCTCOUNT ( 'Table'[Store] )
VAR Height = 20 
VAR MAX1 =
    MAXX ( ALL ( 'Table' ), [M.ThisYear] )
VAR MAX2 =
    MAXX ( ALL ( 'Table' ), [M.LastYear] )
VAR W =
    IF ( MAX1 >= MAX2, MAX1 / 100, MAX2 / 100 ) 
VAR Title_Width = 42
VAR DumbbellTable =
    ADDCOLUMNS (
        SUMMARIZE (
            'Table',
            'Table'[Store],
            "Color_Line", IF ( [M.ThisYear] <= [M.LastYear], "DarkGrey", "DimGray" ),
            "Value_Left", MIN ( [M.ThisYear], [M.LastYear] ),
            "Value_Right", MAX ( [M.ThisYear], [M.LastYear] ),
            "Color_Circle_Left", IF ( [M.ThisYear] <= [M.LastYear], "DarkCyan", "Tomato" ),
            "Color_Circle_Right", IF ( [M.ThisYear] > [M.LastYear], "DarkCyan", "Tomato"),
            "Index", RANKX ( ALLSELECTED ( 'Table' ), [M.ThisYear],,, DENSE )
        ),
        "Line",
            "<line x1='" & Title_Width + [Value_Left] / W & "' y1='" & ( [Index] - 1 ) * Height + 10 & "' x2='" & Title_Width + [Value_Right] / W & "' y2='" & ( [Index] - 1 ) * Height + 10 & "'  style='stroke:" & [Color_Line] & ";stroke-width:1' />",
        "Circle_Left",
            "<circle cx='" & Title_Width + [Value_Left] / W & "' cy='" & ( [Index] - 1 ) * Height + 10 & "'  r='1.5'" & " fill=" & "'" & [Color_Circle_Left] & "'/>",
        "Circle_Right",
            "<circle cx='" & Title_Width + [Value_Right] / W & "' cy='" & ( [Index] - 1 ) * Height + 10 & "'  r='1.5'" & " fill=" & "'" & [Color_Circle_Right] & "'/>",
        "Text_Left",
            "<text x='" & Title_Width + [Value_Left] / W & "' y='" & ( [Index] - 1 ) * Height + 8 & "'  fill='black' text-anchor='Middle' font-size='5'>" & [Value_Left] & "</text>",
        "Text_Right",
            "<text x='" & Title_Width + [Value_Right] / W & "' y='" & ( [Index] - 1 ) * Height + 8 & "'  fill='black' text-anchor='Middle' font-size='5'>" & [Value_Right] & "</text>",
        "Title",
            "<text x='0' y='" & ( [Index] - 1 ) * Height + 12 & "' fill='black' text-anchor='Right' font-size='5' >" & [Store] & "</text>"
    )
VAR Bar =
    CONCATENATEX (
        DumbbellTable,
        [Line] & [Circle_Left] & [Circle_Right] & [Text_Left] & [Text_Right] & [Title]
    )
VAR SVG = "data&colon;image/svg+xml;utf8," & "<svg xmlns='http://www.w3.org/2000/svg' height='" & ItemCount * Height & "' width='" & 120 + Title_Width & "' >" & Bar & "<line x1='" & Title_Width & "' y1='0'" & " x2='" & Title_Width & "' y2='" & ItemCount * Height & "' style='stroke:grey;stroke-width:0.1'/>" & "</svg>" 
RETURN
    SVG

 

Dumbbell+Bar Chart

 

DumbbellBarChart = 
VAR ItemCount =
    DISTINCTCOUNT ( 'Table'[Store] )
VAR Height = 20
VAR MAX1 =
    MAXX ( ALL ( 'Table' ), [M.ThisYear] )
VAR MAX2 =
    MAXX ( ALL ( 'Table' ), [M.LastYear] )
VAR W =
    IF ( MAX1 >= MAX2, MAX1 / 100, MAX2 / 100 )
VAR Title_Width = 42
VAR DumbbellTable =
    ADDCOLUMNS (
        SUMMARIZE (
            'Table',
            'Table'[Store],
            "Color_Line", IF ( [M.ThisYear] <= [M.LastYear], "DarkGrey", "DimGray" ),
            "Value_Left", MIN ( [M.ThisYear], [M.LastYear] ),
            "Value_Right", MAX ( [M.ThisYear], [M.LastYear] ),
            "Color_Circle_Left", IF ( [M.ThisYear] <= [M.LastYear], "DarkCyan", "Tomato" ),
            "Color_Circle_Right", IF ( [M.ThisYear] > [M.LastYear], "DarkCyan", "Tomato" ),
            "Index", RANKX ( ALLSELECTED ( 'Table' ), [M.ThisYear],,, DENSE )
        ),
        "Line",
            "<line x1='" & Title_Width + [Value_Left] / W & "' y1='" & ( [Index] - 1 ) * Height + 10 & "' x2='" & Title_Width + [Value_Right] / W & "' y2='" & ( [Index] - 1 ) * Height + 10 & "'  style='stroke:" & [Color_Line] & ";stroke-width:1' />",
        "Circle_Left",
            "<circle cx='" & Title_Width + [Value_Left] / W & "' cy='" & ( [Index] - 1 ) * Height + 10 & "'  r='1.5'" & " fill=" & "'" & [Color_Circle_Left] & "'/>",
        "Circle_Right",
            "<circle cx='" & Title_Width + [Value_Right] / W & "' cy='" & ( [Index] - 1 ) * Height + 10 & "'  r='1.5'" & " fill=" & "'" & [Color_Circle_Right] & "'/>",
        "Text_Left",
            "<text x='" & Title_Width + [Value_Left] / W & "' y='" & ( [Index] - 1 ) * Height + 8 & "'  fill='black' text-anchor='Middle' font-size='5'>" & [Value_Left] & "</text>",
        "Text_Right",
            "<text x='" & Title_Width + [Value_Right] / W & "' y='" & ( [Index] - 1 ) * Height + 8 & "'  fill='black' text-anchor='Middle' font-size='5'>" & [Value_Right] & "</text>",
        "Title",
            "<text x='0' y='" & ( [Index] - 1 ) * Height + 12 & "' fill='black' text-anchor='Right' font-size='5' >" & [Store] & "</text>",
        "Rect",
            "<line x1='" & Title_Width & "' y1='" & ( [Index] - 1 ) * Height + 10 & "' x2='" & Title_Width + [M.ThisYear] / W & "' y2='" & ( [Index] - 1 ) * Height + 10 & "'  style='stroke:RosyBrown;stroke-width:6;stroke-opacity:0.3' />"
    )
VAR Bar =
    CONCATENATEX (
        DumbbellTable,
        [Line] & [Circle_Left] & [Circle_Right] & [Text_Left] & [Text_Right] & [Title] & [Rect]
    )
VAR SVG = "data&colon;image/svg+xml;utf8," & "<svg xmlns='http://www.w3.org/2000/svg' height='" & ItemCount * Height & "' width='" & 120 + Title_Width & "' >" & Bar & "<line x1='" & Title_Width & "' y1='0'" & " x2='" & Title_Width & "' y2='" & ItemCount * Height & "' style='stroke:grey;stroke-width:0.1'/>" & "</svg>" 
RETURN
    SVG