There are probably a lot of ways to accomplish that. I'd personally skip the table calculations if your data stays in the format it is.
Here's my solution. I'm thinking of this as 6 different categories for color - Compliance High/Med/Low and the corresponding Noncompliance High/Med/Low. I calculate each percentage using calculated fields, in 3 steps:
1) ComplianceFlag (if Yes then 1 else 0)
2) CompliancePercent (sum ComplianceFlag / sum Number of Records)
3) ComplianceColorCodes (categorizing as High, Medium, and Low for your color-coding above)
And I do the same with Noncompliance.
Then, a dual axes chart, with CompliancePercent on one column axis, Avg(Number of Records) as the other. (That Average Number of Records will always = 1; purpose is to put a solid 100% behind every record. We can then fill consider this the remainder of the 100% bar which we can color however we choose.) Make sure stack marks is on...
All bars should now stack to 100%. (If you don't see CompliancePercent in front, you have to right-click the Number of Records axis and select Move to Back.)
So now we apply different color coding to each axis. Under the Marks page main menu (upper right corner drop-down box), choose Multiple Mark Types. Select the CompliancePercent field using the arrows below on the Marks page, and drag the measure ComplianceColorCodes into the Color selection. Then select the Number of Records field, and drag the measure NoncomplianceColorCodes into that Color selection.
Now it should be all formatting. Change your legend colors, format the axes, etc.
Hope this helps - as I said, I think there are a lot of solutions, this is one way I'd choose to do it.
ComplianceSolution.twbx.zip 26.6 KB
Oh man, that's a cool trick with the 100% background bar. Never would have thought of that.
Does the job perfectly. Thanks!