Combine Table & Stacked Bar Chart

[Anonymous]

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 added 2 charts table & stacked bar chart and applied 'sent back' for stacked bar chart. I have 3 slicers.

If i select only one or two names then the report looks like this.

But i am looking to align this bar straight to the name instead of shrinking to its entire size. is it possible?

[Greg_Deckler]

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

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]

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

Approver	count of projects	Average of Duration	%3 Days Outstanding
a1	23	23	100%
a2	1	102	100%
a3	2	1	0%
a4	4	68	100%
a5	1	8	100%
a6	1	23	100%
a7	11	8	100%
a8	22	10	100%
a9	4	1	0%
a10	7	19	100%
a11	13	76	100%
a12	1	102	100%
a13	19	46	95%
a14	141	39	75%
a15	2	37	50%
a16	9	69	100%
a17	6	23	33%
a18	1	81	100%
a19	1	3	0%

[Anonymous]

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? 

[Greg_Deckler]

@Anonymous  - It is under Conditional Formatting oddly enough.

[Anonymous]

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

[Greg_Deckler]

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

@Greg_Deckler 

I just created a new measure and applied your code.  I am getting the text only instead of bars.

[Anonymous]

[Greg_Deckler]

Another trick, if your rows come out really really tall, you can edit the Image Height in the Grid area of the visual.

[Greg_Deckler]

Oh, sorry @Anonymous - you need to set the Data Category for the measure/column to Image URL. Click on the measure/column and then click on the Measure Tools or Column Tools. Change the Date Category to Image URL.

[Anonymous]

@Greg_Deckler , I am not seeing any image now. I followed your options. I changed like Model --> Selected 'SVG' column --> Advanced --> Data Category --> Image URL

[Anonymous]

I referred few other posts here and found the issue.

data:

I changed it to data: 

[Anonymous]

It is fine, but i am looking for 3 colors based on 'Age Group'. I just implemented in the actual report, but i am getting 'MAX function only accepts a column reference as the argument number 1".

[Anonymous]

I tried my level best to bring the expected result.

SVG Stacked Data Bar =
VAR __Table = 'my'

VAR __Color1 = IF (AVERAGE(my[<3 age group])=1,"Green","Yellow") VAR __Color2 = IF (AVERAGE(my[<3 age group])=1,"Yellow","Green")
VAR __ShapeWidth2 = count(my[ProjectID])*500
VAR __header = "data: 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]

SVG Stacked Data Bar =

VAR __Table = 'my'

VAR __Color1 = IF (AVERAGE(my[<3 age group])=1,"Green") VAR __Color2 = IF (AVERAGE(my[>3 & <6 group])=1,"Yellow","Red")

VAR __ShapeWidth2 = count(my[ProjectID])*500

VAR __header = "data: 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

i'm getting like above which is not correct.

'Age Group' is not applied, BAR length is also very small.

[Anonymous]

@Greg_Deckler , Can you look into the issue please and provide the solution?

[Greg_Deckler]

It's going to be next to impossible to troubleshoot this without the data @Anonymous  - can you share the PBIX or a representative sample?

[Anonymous]

@Greg_Deckler 

Please find the mock data.

Approver	ProjectID	Duration
a1	p1	2
a1	p2	4
a1	p3	10
a2	l1	1
a2	l2	1
a2	l3	1
a3	f1	15
a3	f2	15
a3	f3	20
a3	f4	20

Calcuated Columns/Measures:

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

[Anonymous]

@Greg_Deckler , is the same data help you to bring the expected result? can you help me on this ?

[Greg_Deckler]

@Anonymous - Sorry, lost track of this thread. Let me take a look at the data you provided and see what I can create.