+ Reply to Thread
Results 1 to 2 of 2

Average count by pivot subtotal

  1. #1
    Registered User
    Join Date
    11-11-2020
    Location
    U.S.
    MS-Off Ver
    2016
    Posts
    1

    Question Average count by pivot subtotal

    Please see the attachment

    I have a pivot table that counts the number of instances of each actor for each location and subtotals by location. I would also like to average the count of instances of each actor for each location. Summarizing by average does not work.

    I think I may need a Power Pivot measure or calculated field.

    Please help!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,559

    Re: Average count by pivot subtotal

    Hello jbfx and Welcome to Excel Forum.
    This proposal employs a helper column, converting the data to a table and Get & Transform/Data Model.
    The helper column (C) named Distinct Ct. is populated using: =IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)=1,1,0)
    Note that the helper column may be hidden and/or moved for aesthetic purposes.
    After the data in A:C is converted to a table the following Power Query advanced editor is used to produce a connection only to the table and produce a data model:
    Please Login or Register  to view this content.
    The following measure (Average) is added to the data model: =COUNT(Table1[Actor])/SUM(Table1[Distinct Ct.])
    In the pivot table that is produced from the data model Distinct Ct. (SUM) and Average are placed in the Values area.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pivot Table Subtotal:Sum & Average
    By jpecor2 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 05-05-2014, 10:08 AM
  2. Pivot: How to get the average of Subtotal
    By Siops in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 10-07-2013, 10:53 AM
  3. Pivot Average of Count
    By zpenacho in forum Excel General
    Replies: 1
    Last Post: 02-14-2012, 01:48 PM
  4. Replies: 2
    Last Post: 01-28-2010, 10:08 AM
  5. [SOLVED] Average of count in pivot tables
    By Dan in NY in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 04:05 PM
  6. Average of count in pivot tables
    By Dan in NY in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 09:05 AM
  7. Average of count in pivot tables
    By Dan in NY in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1