Pivot Table with multiple grand total calculations

Question

Hello,I'm trying to create a pivot table that has multiple total columns.&nbsp; I can get the totals, but it adds a column for Average and MIN (Best finish) to each FIN (finishing position) in the body of the pivot table. I'd like to create a pivot that has (or at least shows) only the 1st FIN under values (it's a count of FIN &amp; only thing I use) and the three total columns.&nbsp;&nbsp;I'm hoping to get it to look like the second pivot. I'm happy if there's a way just to automatically close those extra columns (for Average &amp; MIN) in the pivot body. At this point I do it manually. I'd really hope to totally get rid of them, it'll make my pivot smaller.&nbsp;Thank you for any ideas to accomplish this.&nbsp;&nbsp;&nbsp;

azure_temir5org · Answer

😎😍

sergeibaklan · Answer

Azure_temir5org&nbsp;
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= "
        &amp; FORMAT (
            SUM ( Table1[V] ),
            "000"
        ) &amp; "; Avr= "
        &amp; FORMAT (
            AVERAGEA ( Table1[V] ),
            "000.0"
        )
)

debidomby · Answer

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 &amp; 20-25 cars a race).Finish Driver1 Brian Bergakker2 Conner Zbozien3 Paul Pelletier4 Craig Everage5 Scott Hantz6 Eric Lee7 Chris Benson8 Justin Schroeder9 Hayden Sprague10 Derrick Sheppard11 Scott Pemberton12 RJ Cornett13 Nick Bailey14 Rob Randa15 Joe Hawes16 Max Fair17 Austin Thiel18 Dean Gassert19 Craig Osenbaugh20 Stan Yee Jr21 John Ledwidge1 Brian Bergakker2 Eric Lee3 Scott Hantz4 Conner Zbozien5 Trevor Berry6 Chris Benson7 Dennis Strickland8 Mike Root9 Justin Schroeder10 George Rangel11 Stan Yee Jr12 Joe Hawes13 Scott Pemberton14 Derrick Sheppard15 Joshua Spindelman16 Bryan Moore17 Paul Pelletier18 James Pankow19 Guy Fire1 Brian Bergakker2 Eric Lee3 Hayden Sprague4 Conner Zbozien5 Scott Hantz6 Scott Pemberton7 Dennis Strickland8 Chris Benson9 George Rangel10 Joshua Spindelman11 Dan Trosen Jr12 James Pankow13 Jimmy Thiel14 Mike Root15 Paul Pelletier16 Bryan Moore17 Harold Fair Jr18 Stan Yee Jr

debidomby · Answer

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.

sergeibaklan · Answer

DebiDomby&nbsp;
Sorry, 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?

Forum Discussion

Pivot Table with multiple grand total calculations

7 Replies

Resources