This can definitely be done, but it's very difficult to show you how without having some idea of the structure of your data. Would you be able to create a mockup of your data and your workbook and attach it here?
Agree with Ken Flerlage. This can for sure be done. A few different ways.
If you could create a simple example database layout in excel and send it on over OR a packaged workbook of the real data (if not divulging any confidential information of course), we could help a lot better.
Any way you could give us what measure/dimensions that you have on the shelves?
Thank you for your feedback. I have created a quick&dirty mockup of my data and the report. In reality, the 2 datasources are two different data sets on different serves. One is a global database of all clients, all portfolio and all securities held with daily portfolio data. The other is a global database of 12'000 securities with 80 different datapoints each for their involvement in various activities. Common identifier is the security ID. My filters are on portfolio id, date, and a selection of some of the 80 activities.
This is actually kind of tricky. It feels like something that should have a simple solution, but I'm struggling to find that simple solution. But here's an option.
Create a new measure with value 1.0 on the columns shelf.
Change to a dual axis then synchronize the axes. Also, on the first axis, remove all pills from the color and detail cards.
Visually, this will look like a stacked bar chart even though the first axis is really just one single long bar behind it.
If you want the tooltip to show the remainder amount, you could trick it by creating a calculated field like this:
// Remainder amount.
Then drag that to the tooltip. Then you can edit the tooltips on each of the axes so that they look the same:
See attached workbook.
I also came up with another option that would use a single axis and would be a true stacked bar chart, but it requires some table unions and other tricky stuff. If you'd like to hear about that method, let me know.
data mock-up.twbx 21.6 KB
Thank you Ken, This approach works fine for my data...