Log In
Register
Home
May 28, 2021
Offline
Hi,
I am new to pivot tables and dashboards. After watching you webinars on creating dashboards I of course decided 'I can do that it's easy'. Hmmmm, well not so much. I believe that 'Raw Data' table in the 'Tables and Data' worksheet may be incorrectly set up. Here is why I think that is so. One, my goal, to be able to slice the sourcing pivot table data by the values in the 'Sprint' column so I can view the information on the dashboard Sprint by Sprint to determine trends. Two, second issue, when I click on the associated Slicer to show data from a different Sprint the chart do not update immediately. there is a lag and in the case of the bar charts both the new data and the prior data are visible briefly.
So I have attached the excel file, tab 'Tables and Data' holds all the underlaying data and tables for the graphs on the Dashboard, and the Dashboard itself.
Hope you can show me where I have gone wrong.
Thanks in advance....
William
May 28, 2021
Offline
So got the data issue resolved. However not the latent data issue with the graphs. Still have the old data present briefly when the new data displays. And all graph loads are slow.
July 16, 2010
Offline
Hi William,
Welcome to our forum! Thanks for sharing your file. I can see that your data was originally in the incorrect layout, but you've fixed that now. The reason the Base Data PivotTable is shwoing the row labels 'stacked' as you put it, is because the layout is set to 'Compact'. You can change this in the PivotTable Design tab > Report Layout > either Outline or Tabular.
As for the charts updating slowly, I'm not sure why that is. You can turn off the 'animation' effect via the File > Options dialog > Advanced tab (For Excel 2016 or 2019) or Ease of Access tab (For Microsoft 365) un-check "Provide Feedback with animation".
Mynda
May 28, 2021
Offline
Mynda,
Thanks for the response.
Still no luck with the graphs. Could it again a data structure issue in the pivot tables themselves? I have the Dates and associated data grouped by Sprints but maybe not correctly? I notice a distinct difference between your pivot tables and mine. I will continue to try and figure it out. If I get an answer I will let you know so we can close this thread.
William
May 28, 2021
Offline
Mynda,
So I am belaboring the point here I know but I am thinking that the data is not set up correctly. So that said I am sending a test workbook with the changed data format that seemed to have solved some of the problems. But as I mentioned prior I am wondering about the pivot table results. Since the data has no real repeatable data but the Sprint ID and we are not looking to roll the data up it has me wondering it this is correct or not.
William
July 16, 2010
Offline
Hi William,
From my point of view your data is in the correct format, but as I don't really know what your expectations are I can't say much more on the topic. By their nature, PivotTables summarise data. If you don't want the data aggregated, then maybe a PivotTable isn't suitable for your needs.
If you can create a mockup of the chart you want, then I can try to help further.
Mynda
May 28, 2021
Offline
Mynda,
Ok the data is good, I need no aggregation just the data for Open and Ideal provided in the stacked bar chart. The same data is shown in the line chart. So with the data good and the pivot good the next real issue it the bar chart update latency. When the slicer is changed to select a sprint the line updates fine but please notice the latency in the bar chart update. Is this from the use of the pivot, the slicer or what. If I use a Pivot Chart I do not get this issue actually the original chart and the PivotTable chart work fine from the slicer, until I move the chart and slicer to another sheet for display in a final presentation layer.
So that is the real issue, the latency when moved to the other sheet, or when the chart is not a Pivot chart.
William
July 16, 2010
Offline
Hi William
Instead of copying the chart and Slicer, try cutting them and pasting them on the sheet you want them on. When I did this there was no latency.
Mynda
May 28, 2021
Offline
Mynda,
No luck. I queried MicroSoft and the response was that if I had Windows 10 20H2 with a particular OS build that this problem could be duplicated. A different OS build did not have that issue. Also for some strange reason a 3d-bar chart would work.
Well, of course I would have the build in question, and the 3d bar chart works. That does not in any way explain why your dashboard works fine and I cannot duplicate the same effect when I try to build my own with the data I provided to you. At this point I suppose I will have to convince management that they either learn to love the 3d charts or they do not get the dashboard 🙂 until I can figure out what is going on.
Thanks for all of the help. I will let you know what the solution finally turns out to be.
1 Guest(s)
