Forum Discussion
Problem with Pivot tables
Hi Gabriel,
If you added data to data model creating the PivotTable, you may add measure like this
and use it instead of the column pivoting the table
Hi SergeiBaklan
Thank you for your assistance. How can I "add data to data model" when creating the pivot table? I do not understand a lot from pivot tables. The worksheet you sent is exactly what I was looking for, but how may I reproduce it in my sheets?
Thank you so much
Gabriel
Gabriel,
Perhaps you already here, that's default option in recent versions of Excel. However, creating new PivotTable be sure what setting is checked
For the existing PivotTable simplest way to check is right click on the table here
If you see Add Measure you work with the data model.
If not, click on More tables and Yes after that to add your existing data to data model.
New PivotTable will be created using the same layout as existing one, however you may lost all formatting. Remove old one and move new on the desired place.
After that right click on table name, select Add measure and in window as in previous post add DAX formula like
=IF(ISFILTERED([Month]),AVERAGE([Value]),SUM([Value]))
using your actual column names. Aggregate your pivot table using this measure instead of the column.