Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Combine Table & Stacked Bar Chart

All,   I have a requirement to develop a report like below. Just removed the approver name due to security reason, think of some names under Approver.   This is not a single chart, i just a...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

There is a 'data bars' option for matrix visualizations that people have used to create condensed bar charts like what you show. However, I do not believe that solution works for stacked bar charts. You could possibly do it with an SVG graphic. Like this for example: https://community.powerbi.com/t5/Quick-Measures-Gallery/SVG-Microchart-Harveyball-and-Harveybox/td-p/489599

 

If you provide sample data I could give this a whirl, it has been awhile since I created an SVG graphic so could be fun. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

I didnt see 'Data bar' option in the Matrix visual. is this introduced in the latest version? I'm using quite old version for now.

 

2.71 (July 2019 Version) - Have to upgrade

 

Please find below the sample data. There is a custom column created for Age Group.

 

Age Group = IF(my[Duration] <= 3, "<=3 Days",IF (my[Duration] >3 && my[Duration] < 6,"> 3 and < 6 Days",">=6 Days"))
 
I used Approver, Count of ProjectID & Age Group for the "100% Stacked Bar Chart".

 

Approvercount of projectsAverage of Duration%3 Days Outstanding
a12323100%
a21102100%
a3210%
a4468100%
a518100%
a6123100%
a7118100%
a82210100%
a9410%
a10719100%
a111376100%
a121102100%
a13194695%
a141413975%
a1523750%
a16969100%
a1762333%
a18181100%
a19130%

 

 

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous  - It is under Conditional Formatting oddly enough.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Yes, I Can see that option, but it looks like it will not fulfil my requirement.

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        6 years ago

        OK this was what I was able to pull together. It's rough and needs work. I couldn't understand how the Age Group column factored in to this so I just kind of went with some data. I'll give you the code. It "works", it's not what I would call pretty. Attached a PBIX but you won't be able to open it unless you upgrade to April 2020.

        SVG Stacked Data Bar = 
    VAR __Table = 'Table'
    VAR __Total = MAX('Table'[Average of Duration]) + MAX('Table'[count of projects])
    VAR __Color1 = IF(MAX('Table'[Average of Duration]) > MAX('Table'[count of projects]),"Red","Green")
    VAR __Color2 = IF(MAX('Table'[Average of Duration]) > MAX('Table'[count of projects]),"Green","Red")
    VAR __Min = MIN(MAX('Table'[Average of Duration]),MAX('Table'[count of projects]))
    VAR __ShapeWidth2 = __Min / __Total * 500
    VAR __header = "data&colon;image/svg+xml;utf8," &
              "<svg xmlns='http://www.w3.org/2000/svg' x='0px' y='0px' width='" & "500" & "' height='210'>"
    VAR __footer = "</svg>"
    VAR __shapeTextSquare1 = "<polygon points=""0,200 " & "500" & ",200 " & "500" & ",0 0,0"" style=""fill:" & __Color1 & ";stroke:" & __Color1 & ";stroke-width:0;fill-rule:evenodd;"" />" 
    VAR __shapeTextSquare2 = "<polygon points=""0,200 " & __ShapeWidth2 & ",200 " & __ShapeWidth2 & ",0 0,0"" style=""fill:" & __Color2 & ";stroke:" & __Color2 & ";stroke-width:0;fill-rule:evenodd;"" />" 
    VAR __SVG = __header & __shapeTextSquare1 & __shapeTextSquare2 & __footer
RETURN
    __SVG

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    It looks like SVG can be the solution for this, but it is bit complex to understand for me (As a beginer to PowerBI)

     

    How about using R for this scenario?