Please note javascript is required for full website functionality.


Power Pivot Principles: More PivotCharts from the Power Pivot Data Model

29 December 2020

Welcome back to the Power Pivot Principles blog. This week, we will continue with looking at creating PivotCharts from the Power Pivot Data Model.

Last week, we talked about creating PivotCharts directly from the Power Pivot window. From a Sales data loaded in the Power Pivot Data Model,

we created a mini dashboard with a PivotChart and a PivotTable.

This week, we will continue to look at more exciting PivotChart features that we can get from the Power Pivot window.

What I particularly like about creating charts from the Power Pivot Data Model is that I can create multiple charts which are automatically aligned. To do this, I navigate to the PivotTable on the Home tab of the Power Pivot window and select ‘Four Charts’. I load them to the new worksheet, and bingo, they are all beautifully shaped together, all I need to do is drag the field to its place.

After formatting one of the charts, I copy and paste it to the other three and change the chart titles. My dashboard now looks like the one below.

There is one more thing that has not been mentioned: a Flattened PivotTable. From the Power Pivot Data Model window, in the Home tab, choose PivotTable -> Flattened PivotTable, and load it into the new worksheet.

The Flattened PivotTable initially looks like the one below.

There is no drill functionality in this type of PivotTable. Therefore, if I want to see Sales by Year and Month, I need to drag the Month to the Rows field, whereby I will get a table like the one below:

To finish, I will need to format the number and rename the fields.  I can right-click on the field and customise the name in ‘Value Field Settings’ like I did last week or just simply edit the cell names from the formula bar.  The Flattened PivotTable now looks much better.

That’s it for this week!

Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.