Visualization using Summary Values from a Pivot Table

Has anybody tried to create a visualization, say a bar or line chart, using summary values already from a pivot table (create child element)? I’ve been trying to do it, but the charts still try to aggregate again like it is still getting the data from the original un-summarized table (before pivot was created). Anybody has some tips?

When you create a child from a pivot table, the data that child uses as a source is the underlying data of the pivot table, not the pivot table itself.
You can adjust the level of aggregation the child is referencing, but it will still be referencing the underlying data.
If you maximize your pivot, you can see the underlying data and what your chart will be referencing.

Does that help?

Thank you Erica.

It would have been nice to build the charts/visualizations on the summarized and computed values of the pivot table itself. I added new columns on the pivot that used the summarized values with calculations. Those are what I wanted to put in the charts. My only option now is to do all those aggregation and calculations in a SQL query (create a new dataset) then do the visualizations.

image001.jpg

The numbers are still there in the data when you create a child table, but format might be different than you expect.
I think that changing the aggregation level of your child table may get you closer to what you are looking for.

Thank you Erica!

As you suggested, I created a child Pivot Table from the original Pivot Table. On the child Pivot Table, I did the Aggregation Level to have all the groupings I needed to create the visualization. Then, I created a visualization (child element) based on the child Pivot Table. It is now showing what I needed!

It works now but it is indeed another step to create a child pivot table on top of a pivot table just to do summary visualizations.

I’m not quite following why the child pivot was needed, and you couldn’t create the summary visualizations and set them to reference the correct level of aggregation for the pivot table.

That said, I also filed a feature request so that child tables of pivots automatically are set to the level of aggregation visible in the pivot table instead of including all the base columns. If that was out default, I think a lot of the confusion could have been avoided.

Hi Erica, it seems that if I don’t create a child pivot table on top of the original pivot table, there is no aggregation level showed on the data source - as it points to the original flat data source. See below using Sigma sample data. Is there another way to look at the aggregation level of the current pivot table?

Aggregation level appears when the parent has multiple levels. It should appear in all of the children of your original pivot table.
How are you creating the summary visualizations? If you are using Single Value Visualizations, you should be able to create them as children of your original pivot table, and select the appropriate aggregation level directly in those visualizations.

That is why I needed to create a child pivot table from the parent pivot table. Yes, a visualization from the parent pivot table is available but the summary values that have been computed on the parent pivot table show as being “aggregated multiple times” on the visual charts as it is still pulling from the original source data! I also needed to add new columns with calculations using the summary values from the parent pivot table. The charts/visualization created from the child pivot table display exactly what I needed. Anyway, I 'm closing this issue now. Thanks for the help, Erica!

1 Like