Forum Discussion
Analyze Data - Help with Structuring Data to Improve Analysis
I have a table of library book circulation figures that I have put into a pivot table. The table shows a certain class of items (fiction, non fiction, etc.) and the circulation numbers for each class across a span of several years. I'd like to use the Analyze Data tool to help me answer some questions for a report I'm writing, but I can't seem to format the questions properly. I'd like for the tool to show me the total of each class across each year in a graph, perhaps a stacked bar graph, or line graph, to indicate trends.
What is the best way to do this? Should I change the labels on my tables to get better answers from the tool? Thanks in advance for any help.
7 Replies
Hi skeskali
Hope attached visualization (Cross Tab or Hierarchical Bar Chart) will help you in analyzing data.
No Code Data Visualization add-in used to create this chart.
You can check here for https://chartexpo.com/utmAction/NCtNVEMreGwrUUorTVNPRlRDTVQr.
And if you are comfortable with Google Sheets, you can find https://chartexpo.com/utmAction/NCtNVEMrZ3MrUUorTVNPRlRDTVQr.
Thanks
Hi skeskali,
You stated: "I'd like for the tool to show me the total of each class across each year in a graph, perhaps a stacked bar graph, or line graph, to indicate trends."
Just for the fun of it, and the only thing you have to change is switch the time periods and the category in your pivot table. Have you tried putting the time periods in the ROWS, and the category in the Columns then make a graph out of it? Just wondering if you have tried that approach. Then a different graph for the location, the same approach or maybe the first value in the column would be the location, then category or probably even better, in the rows the first value would be the year, then location and in the column would be the categories. (recommended)
That approach might yield a graph chart that looks something like this:
cheers
MarGinesMicrosoft
Thanks for sending a question about the Analyze Data feature. Analyze Data can’t currently create suggestions based on PivotTables, so you’ll want to make sure you are running it on your original data.
If you have a column for each of the years, you’ll want to mention each one specifically, for example: 2017, 2018, 2019 and 2020 by item class as column chart (or a different chart type). Note that it's easier to ask questions over time when you have a single column with multiple date values.
If this doesn't work, you might want to make sure your data is formatted as a table. Please select all of it and click on Format as Table in the Home tab and then try Analyze Data again.
Let us know if this doesn’t solve your issue.
Thanks for your feedback,
Mar
- MarGines
Sorry that didn't work. I wasn't suggesting you changed the structure of your data, just that the scenarios for which we have optimized the feature so far typically have dates grouped in columns. We continue to iterate and improve the quality of the feature and we'll incorporate your feedback as we do so.
Thanks again for reaching out, skeskali
Mar
