Hi experts.
I'm facing a challenge in Excel. I am preparing a dashboard in Excel which relies on slicer to filter multiple charts at a time. Therefore the charts are all created from the same table of data via a pivot table. So far that works well.
I want to add a stacked bar chart which pulls data from two different rows of data per bar. So bar one will use column 1 + 2, bar 2 will use column3+4, bar 3 will use column 5+6 and so on.
The first column of a set has either a -1 or nothing while the second column of a set has a +1 or nothing (I could combine it into one column if needed, in below screenshot column TS would be such column as combination of both technical solution columns).
Issue is: Since I use one slicer for multiple charts to filter I cannot transform/change the structure of the data table much. If I do so (e.g. via powerquery would be simple) the slicer cannot be linked to the newly created chart.
Also I cannot use the Excel Data model since I want to display the dashboard in a sharepoint which crashes once the Data Model comes into play. Powerquery seem work well)
But based on my data structure I cannot get the pivot structure as needed to display such charts.
What have I missed? How could I get there. Any idea?
Data table which acts as source (plus a few other column used for the other charts & filtering)
data_table.png
what I would love to get out of it (based on a mockup table/data I created)
chart_required_(mock_up).png
Table structure used to produce above mock up chart
pivot_required(mock_up).png
Data structure used (mock up)
data_structure(mock_up).png
Bookmarks