Forum Discussion
Unable to stop values field from calculating in pivot tables
I’ve created a pivot table where all the other fields are set up correctly. However I am unable to just include data in the “values” without it performing a calculation. Tried multiple times to select “no calculation” but doesn’t seem to work.
Device is MacBook Pro. Excel 2016
9 Replies
The Values area of a PivotTable is inherently an aggregation area. Move the field to Rows or Columns to list records, then use Design > Report Layout > Show in Tabular Form. If the goal is simply a filtered list, keep the data in an Excel Table and filter the source instead of forcing a PivotTable to behave like a raw-data view.
Add measure with this formula -
=CONCATENATEX(Table Name,[Field name],",")
"Device is MacBook Pro. Excel 2016". Does Excel on Mac works with data model now, really?
- i did it after many of tries , format the original column as text not number and in pivot table put the column in value section with sum formula , good luck
A pivot table always summarizes the values of the field(s) in the Values area using one of the functions in the list (Sum, Count, Min, ...).
- Thanks, but now I'm curious what's the best way to just display the values. e.g. number of occurrences without counting? For example, display the number of repetitions performed with a certain load. If 30lbs, for 10 repetitions performed 4 times. What is the best way to report this?
The pivot table value fields counts but that's not what is needed.It would help if you could attach a sample workbook that gives us an idea what the data look like and what the desired result would be.
