Forum Discussion

hchava5292's avatar
hchava5292
Frequent Visitor
1 hour ago

Need stacked Bar chart in Table

I have 2 measures named admission and readmissions. My requirement is instead of showing these 2 measures as separate fields in table visual and turning on data bars I want them to show exactly like in attached image inside the table visual. Please let me know how I can implement this in power BI. In the same table visual i have 3 columns one is dimension which will change dynamically based on user selection and readmission/admission is second field and 3rd field is also a measure which change dynamically based on selected metric.

 

Need Help
Tips and Tricks

3 Replies

  • Lodha_Jaydeep's avatar
    Lodha_Jaydeep
    Icon for Solution Sage rankSolution Sage
    33 minutes ago

    Hi hchava5292​,

    There is no any default support for adding the stacked bar chart(As shown in img). Dynamic bar colur wouldn't help you over here in a way you need to have a chart.

    I have similar type of requirenment and I accomplished it by using the SVG Image measures to create the bar along with the legend and convert that measure type to Image URL and use that in the table visual and it will start showing like you showed above.

     SVG Measure Example

    Patient Count Stacked SVG = 
VAR Thresh = SELECTEDVALUE ( 'Days Since Last Activity'[Days], 90 )
VAR T = fabric_UNI_ActivePatient

VAR Same = COALESCE ( CALCULATE ( DISTINCTCOUNT ( T[PatientKey] ), KEEPFILTERS ( NOT ISBLANK ( T[LastActiveDate] ) ), KEEPFILTERS ( T[DaysSinceLastActivity] <= Thresh ), REMOVEFILTERS ( T[PrimaryLoc_Match_Status] ), T[PrimaryLoc_Match_Status] = "RN + Pt Same PrimLoc" ), 0 )
VAR Diff = COALESCE ( CALCULATE ( DISTINCTCOUNT ( T[PatientKey] ), KEEPFILTERS ( NOT ISBLANK ( T[LastActiveDate] ) ), KEEPFILTERS ( T[DaysSinceLastActivity] <= Thresh ), REMOVEFILTERS ( T[PrimaryLoc_Match_Status] ), T[PrimaryLoc_Match_Status] = "RN + Pt Diff PrimLoc" ), 0 )
VAR Tot = Same + Diff

VAR AllRows = FILTER ( ADDCOLUMNS ( ALLSELECTED ( T[Sub Region], T[PrimRN_Initials] ), "@R", CALCULATE ( DISTINCTCOUNT ( T[PatientKey] ), KEEPFILTERS ( NOT ISBLANK ( T[LastActiveDate] ) ), KEEPFILTERS ( T[DaysSinceLastActivity] <= Thresh ), REMOVEFILTERS ( T[PrimaryLoc_Match_Status] ), T[PrimaryLoc_Match_Status] IN { "RN + Pt Same PrimLoc", "RN + Pt Diff PrimLoc" } ) ), NOT ISBLANK ( T[Sub Region] ) )
VAR MaxA = MAX ( MAXX ( AllRows, [@R] ), Tot )

VAR MaxW = 450
VAR NormW = 360
VAR RawW = SWITCH ( TRUE (), Tot <= 0, 0, DIVIDE ( Tot, MaxA ) * MaxW )

VAR RawSameW = DIVIDE ( Same, Tot, 0 ) * RawW
VAR RawDiffW = DIVIDE ( Diff, Tot, 0 ) * RawW

VAR SameW = IF ( Same > 0, ROUND ( MAX ( 5, RawSameW ), 1 ), 0 )
VAR DiffW = IF ( Diff > 0, ROUND ( MAX ( 5, RawDiffW ), 1 ), 0 )

VAR SameX = 0
VAR DiffX = SameW
VAR TotX = SameW + DiffW + 15
VAR CanvasW = MaxW + 100

RETURN
    IF ( Tot = 0, BLANK (),
        "data:image/svg+xml;utf8,<svg xmlns='http://www.w3.org/2000/svg' width='" & CanvasW & "' height='30' viewBox='0 0 " & CanvasW & " 30'>"
        & IF ( SameW > 0, "<rect x='" & SameX & "' y='3' width='" & SameW & "' height='25' fill='%23B07AA1'/>", "" )
        & IF ( DiffW > 0, "<rect x='" & DiffX & "' y='3' width='" & DiffW & "' height='25' fill='%23AAAAFF'/>", "" )
        & IF ( Same > 0, "<text x='" & (SameX + SameW / 2) & "' y='21' font-family='Arial' font-size='18' font-weight='bold' text-anchor='middle' fill='white'>" & Same & "</text>", "" )
        & IF ( Diff > 0, "<text x='" & (DiffX + DiffW / 2) & "' y='21' font-family='Arial' font-size='18' font-weight='bold' text-anchor='middle' fill='black'>" & Diff & "</text>", "" )
        & "<text x='" & TotX & "' y='22' font-family='Arial' font-size='20' font-weight='600' fill='black'>" & Tot & "</text></svg>"
    )

    I hope this helps, please consider as accepted solution and give some kudos.
    Thanks!

    • Lodha_Jaydeep's avatar
      Lodha_Jaydeep
      Icon for Solution Sage rankSolution Sage
      23 minutes ago

      hchava5292​, You can also use the custom visuals (Deneb, HTML etc...) Above is the one of the work around in case if you don't need to go beyond the default visuals.

      Also This might need the custom tooltip as the SVG measure is being used. (You can create the SVG tooltip as well or drag column to tooltip fields which you want to show). Else tooltip looks messy if you don't need you can turn off also.

      SVG Tool tip Example:




  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    22 minutes ago

    Hi hchava5292​ 

    You can create a measure that returns an SVG image. The one below was created with the help of AI.

    Stacked Bar Chart SVG = 
VAR _ChartWidth = 600
VAR _BarHeight = 40
VAR _ActualBarHeight = 24
VAR _VerticalOffset = 10
VAR _BarY =
    DIVIDE(_BarHeight - _ActualBarHeight, 2) + _VerticalOffset

VAR _FontSize = 24
VAR _LabelVerticalOffset = 10
VAR _LabelY = 26 + _LabelVerticalOffset

VAR _LabelOffset = 8
VAR _LabelWidth = 110
VAR _SVGWidth = _ChartWidth + _LabelOffset + _LabelWidth

VAR _CurrentMonthRevenue =
    [Total Revenue]

VAR _MaxMonthlyRevenue =
    MAXX(
        ALLSELECTED(
            'Dates'[Month Short],
            'Dates'[Month Number]
        ),
        CALCULATE([Total Revenue])
    )

VAR _Categories =
    ADDCOLUMNS(
        ALLSELECTED('Category'[Category]),
        "@Order",
            SWITCH(
                'Category'[Category],
                "Apparel", 1,
                "Automotive", 2,
                "Cosmetics", 3,
                "Electronics", 4,
                "Homegoods", 5
            ),
        "@Revenue",
            [Total Revenue]
    )

VAR _Segments =
    ADDCOLUMNS(
        _Categories,
        "@Color",
            SWITCH(
                'Category'[Category],
                "Cosmetics", "#4472C4",
                "Automotive", "#ED7D31",
                "Homegoods", "#A5A5A5",
                "Apparel", "#FFC000",
                "Electronics", "#70AD47"
            ),
        "@SegmentWidth",
            DIVIDE([@Revenue], _MaxMonthlyRevenue) * _ChartWidth
    )

VAR _SVGSegments =
    CONCATENATEX(
        _Segments,
        VAR _Order = [@Order]
        VAR _X =
            COALESCE(
                SUMX(
                    FILTER(
                        _Segments,
                        [@Order] < _Order
                    ),
                    [@SegmentWidth]
                ),
                0
            )
        RETURN
            "<rect x='" & FORMAT(_X, "0.00", "en-US") &
            "' y='" & _BarY &
            "' width='" &
            FORMAT([@SegmentWidth], "0.00", "en-US") &
            "' height='" & _ActualBarHeight &
            "' fill='" & [@Color] & "'/>",
        "",
        [@Order], ASC
    )

VAR _BarEndX =
    DIVIDE(
        _CurrentMonthRevenue,
        _MaxMonthlyRevenue
    ) * _ChartWidth

VAR _LabelX =
    _BarEndX + _LabelOffset

VAR _FormattedRevenue =
    SWITCH(
        TRUE(),
        ABS(_CurrentMonthRevenue) >= 1000000000,
            FORMAT(DIVIDE(_CurrentMonthRevenue, 1000000000), "0.##") & "B",
        ABS(_CurrentMonthRevenue) >= 1000000,
            FORMAT(DIVIDE(_CurrentMonthRevenue, 1000000), "0.##") & "M",
        ABS(_CurrentMonthRevenue) >= 1000,
            FORMAT(DIVIDE(_CurrentMonthRevenue, 1000), "0.##") & "K",
        FORMAT(_CurrentMonthRevenue, "#,##0.##")
    )

VAR _TotalLabel =
    "<text x='" & FORMAT(_LabelX, "0.00", "en-US") &
    "' y='" & _LabelY & "' text-anchor='start' " &
    "font-family='Arial' font-size='" & _FontSize & "' fill='#404040'>" &
    _FormattedRevenue &
    "</text>"

RETURN
    IF(
        NOT ISBLANK(_CurrentMonthRevenue)
            && _MaxMonthlyRevenue > 0,
        "data:image/svg+xml;utf8," &
        "<svg xmlns='http://www.w3.org/2000/svg' " &
        "width='" & _SVGWidth &
        "' height='" & _BarHeight &
        "' viewBox='0 0 " & _SVGWidth &
        " " & _BarHeight & "'>" &
        _SVGSegments &
        _TotalLabel &
        "</svg>"
    )

    Please see the attached pbix.