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
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

        I'm sorry the underlying data used to derive the table report as below.

         

        ApproverProjectIDDuration
        a1p12
        a1p24
        a1p310
        a2l11
        a2l21
        a2l31
        a3f115
        a3f215
        a3f320
        a3f420

         

        projects>3 days = IF (my[Duration] >3, 1, 0)

        count of projects = DISTINCTCOUNT(my[ProjectID])
        %3 Days Outstanding = SUM(my[projects>3 days])/[count of projects]
        Age Group = IF(my[Duration] <= 3, "<=3 Days",IF (my[Duration] >3 && my[Duration] < 6,"> 3 and < 6 Days",">=6 Days"))