Forum Discussion
Pivot Table with multiple grand total calculations
Hello, I'm trying to create a pivot table that has multiple total columns. I can get the totals, but it adds a column for Average and MIN (Best finish) to each FIN (finishing position) in the b...
Afraid you can't to show few grand totals without showing the fields. As workaround, creating PivotTable you may add data to data model and create proper measure with combined Grand Total to use instead of initial fields.
Didn't catch how exactly your data is structured, thus on such sample
measure is
NewField :=
IF (
HASONEFILTER ( Table1[ID] ),
MAX ( Table1[B] ),
"Sum= "
& FORMAT (
SUM ( Table1[V] ),
"000"
) & "; Avr= "
& FORMAT (
AVERAGEA ( Table1[V] ),
"000.0"
)
)
- One more question... Can you run macros on Pivot tables? That would be the best solution. Once you do it once, my columns never change. 26 is the most I'll have. That would be a great solution.
Yes, VBA works with PivotTable, but that's not my territory.
- Thanks... I'll try it. I never thought about recording a macro for it until now ... That'll actually solve my problem.
- Thanks for verifying what I always suspected, you need the columns in the pivot table body to get the grand totals. Below is how the data is set up. I grows 40-50 lines each year (2 races & 20-25 cars a race).
Finish Driver
1 Brian Bergakker
2 Conner Zbozien
3 Paul Pelletier
4 Craig Everage
5 Scott Hantz
6 Eric Lee
7 Chris Benson
8 Justin Schroeder
9 Hayden Sprague
10 Derrick Sheppard
11 Scott Pemberton
12 RJ Cornett
13 Nick Bailey
14 Rob Randa
15 Joe Hawes
16 Max Fair
17 Austin Thiel
18 Dean Gassert
19 Craig Osenbaugh
20 Stan Yee Jr
21 John Ledwidge
1 Brian Bergakker
2 Eric Lee
3 Scott Hantz
4 Conner Zbozien
5 Trevor Berry
6 Chris Benson
7 Dennis Strickland
8 Mike Root
9 Justin Schroeder
10 George Rangel
11 Stan Yee Jr
12 Joe Hawes
13 Scott Pemberton
14 Derrick Sheppard
15 Joshua Spindelman
16 Bryan Moore
17 Paul Pelletier
18 James Pankow
19 Guy Fire
1 Brian Bergakker
2 Eric Lee
3 Hayden Sprague
4 Conner Zbozien
5 Scott Hantz
6 Scott Pemberton
7 Dennis Strickland
8 Chris Benson
9 George Rangel
10 Joshua Spindelman
11 Dan Trosen Jr
12 James Pankow
13 Jimmy Thiel
14 Mike Root
15 Paul Pelletier
16 Bryan Moore
17 Harold Fair Jr
18 Stan Yee JrSorry, I didn't catch which columns do you need and what they are. In general if columns are not needed at all you may create calculated field with same result as in Grand Total now and use only it.
Perhaps you may share sample file and indicate which result is desirable?
