How to access all columns in a Report when creating a Dashboard Chart?
I have a report that is summarizing project costs, sorted first by the line-item code in the budget and then by Quarter of Completion afterword.
I am hoping to represent this data graphically with a dashboard chart. The goal is to have a Bar Graph that shows the cost in each quarter, clustered by the budget item. When pulling the data from the Report, only the Primary (Sheet source name) and the Cost columns are available. (see pic below)
Essentially, I want to summarize 3 layers of information in the chart:
- Budget Delineation --> Quarter of Completion --> Cost
Hope is to have a color code for each Budget Delineation, then have x-val be the Quarter of Completion and y-val be the cost.
I tried to just pull the data directly from the source Sheet but always get the "Selected data can not be charted" error. I believe this is because many of the rows in the source sheet are blank in those columns, hence the filtering in the Report and the initial attempt to source the chart data there.
How can I make this graph? Do I need to make an entirely new Sheet that pulls the same filtered data from the source Sheet as the report? If so, what is the best approach to do that?
Apologies for the long ranting post. Your help is very much appreciated!