+ Reply to Thread
Results 1 to 6 of 6

Pivoting for average of max values per distinct ID

  1. #1
    Registered User
    Join Date
    06-07-2021
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    3

    Question Pivoting for average of max values per distinct ID

    Excel Type (Windows)

    Excel Version (Office 365)

    Excel Environment (desktop)

    Knowledge Level (Intermediate)

    Sample data attached.

    I have a data problem that I am struggling with relating to max values and would really appreciate some help from the experts! I would normally just pivot to find max values, however in this case I require the information in columns B and C for data slicing. Here is some sample data in the format I am analysing:


    + A B C D
    1 Row ID Data Tag Data Group IsRelevant
    2 28001476 Data 1 Data Group 1 0
    3 28001476 Data 5 Data Group 4 100
    4 28001476 Data 5 Data Group 4 100
    5 28001476 Data 5 Data Group 4 100
    6 28001476 Data 5 Data Group 4 100
    7 28001476 Data 5 Data Group 4 100
    8 28001476 Data 5 Data Group 4 100
    9 28001476 Data 5 Data Group 4 100
    10 28001476 Data 5 Data Group 4 100
    11 28001476 Data 5 Data Group 4 100
    12 28001476 Data 6 Data Group 6 100
    13 28001476 Data 6 Data Group 6 100
    14 28001476 Data 6 Data Group 6 100
    15 28001476 Data 6 Data Group 6 100
    16 28001476 Data 6 Data Group 6 100
    17 28001476 Data 6 Data Group 6 100
    18 28001476 Data 6 Data Group 6 100
    19 28001476 Data 6 Data Group 6 100
    20 28001476 Data 6 Data Group 6 100
    21 28001476 Data 6 Data Group 6 100
    22 28001476 Data 6 Data Group 6 100
    23 28001477 Data 3 Data Group 3 100
    24 28001477 Data 3 Data Group 3 100
    25 28001477 Data 3 Data Group 3 100
    26 28001477 Data 3 Data Group 3 100
    27 28001477 Data 3 Data Group 3 100
    28 28001477 Data 3 Data Group 3 100
    29 28001477 Data 7 Data Group 4 100
    30 28001477 Data 7 Data Group 4 100
    31 28001477 Data 7 Data Group 4 100
    32 28001478 Data 6 Data Group 6 0
    33 28001478 Data 6 Data Group 6 0
    34 28001478 Data 6 Data Group 6 0
    35 28001479 Data 3 Data Group 3 0
    36 28001479 Data 3 Data Group 3 0
    37 28001479 Data 3 Data Group 3 0
    38 28001480 Data 3 Data Group 3 0
    39 28001480 Data 3 Data Group 3 0
    40 28001480 Data 3 Data Group 3 0
    41 28001480 Data 3 Data Group 3 0
    42 28001480 Data 3 Data Group 3 0
    43 28001480 Data 3 Data Group 3 0
    44 28001480 Data 3 Data Group 3 0
    45 28001480 Data 3 Data Group 3 0
    46 28001480 Data 4 Data Group 4 0
    47 28001482 Data 2 Data Group 2 100
    48 28001482 Data 2 Data Group 2 100
    49 28001482 Data 6 Data Group 6 100

    I need to pivot the data so that I can slice it by Data Group and Data, showing values for the maximum value of IsRelevant for each distinct Row ID, averaged accross Row IDs.

    My pivot should ultimately look like the below, however this example just shows the mean of all IsRelevant values:
    pivot.PNG

    I am using IsRelevant as a flag (either 0 or 100) for whether it meets some seperate criteria or not, and so I am looking to end up with the % of rowIDs within each Data Group and Data value.

    The formula, in words, for each row in the pivot table would be:

    (Sum of (Max. IsRelevant value per distinct Row ID)) / (number of distinct Row IDs within the Data Group/Data)

    I feel that there is a solution here using PowerPivot, however I can't quite get it to work. I have so far tried the following, but am not getting the correct results due to summing incorrectly:
    =DIVIDE(SUM(Range[IsRelevant]),DISTINCTCOUNT(Range[Row ID])
    I think I need help replacing the SUM in the below with some way of calculating the SUM of the Max IsRelevant value for each distinct RowID.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,089

    Re: Pivoting for average of max values per distinct ID

    Hello cbro4 and Welcome to Excel Forum.
    To attempt to understand, there are three distinct Row ID's for Data Group 3 Data 3. Of those three distinct Row ID's 1 has a IsRelevant value of 100 and the other 2 have IsRelevant values of zero, so the percentage should be 33%.
    Is that correct and if not please tell us the correct percentage along with an explanation of how it is calculated.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,089

    Re: Pivoting for average of max values per distinct ID

    I am going to post this in the event that I may be correct about the calculation of percentages.
    1. Added another column to the source table populated using: =(COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2,D$2:D2,100)=1)+0
    2. Added a measure to the data model > Percentage:=SUM([Distinct 100's])/[Distinct Count of Row ID]
    Let us know if you have any questions.

  4. #4
    Registered User
    Join Date
    06-07-2021
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Pivoting for average of max values per distinct ID

    Hi JeteMc - thank you so much for the reply, yes this is the exact solution to the problem, amazing!

    The final results in the pivot table are exactly what I was looking for. I will do some research later to try and understand the first part with the COUNTIFS, I may need to clarify a couple of points once I have done this.

    Thanks again!

  5. #5
    Registered User
    Join Date
    06-07-2021
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Pivoting for average of max values per distinct ID

    Hi again JeteMc - I understand everything except for the "=1" in the COUNTIFS formula. This appears to revert the count of rows matching the criteria to a TRUE/FALSE, and only sets the first matching row to TRUE. I don't understand a) why it reverts to TRUE/FALSE (I can't seem to find info on this on Google), and b) why it would only count the first matching row per RowID as TRUE?

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,089

    Re: Pivoting for average of max values per distinct ID

    COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2,D$2:D2,100) yields the number of times that a Row ID for Data within a Data Group has a value of 100.
    COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2,D$2:D2,100)=1 as you state converts that into TRUE/FALSE (Boolean) values. However it seems that Boolean values can not be counted using the COUNT function in a measure in the Data Model.
    (COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2,D$2:D2,100)=1)+0 converts the TRUE/FALSE values to 1's and 0's (zeros) which can be used by the SUM function in the Data Model.
    I only wanted to count 1 row per Row ID for Data within a Data Group that has a value of 100 so as to be used in producing a measure that returns the fraction of IsRelevant values of 100 to the Distinct Count of Row ID's.
    I hope that this makes sense. Please let us know if you have any questions.

+ 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. Count distinct, min, max and average on IF condition
    By ashishkhurana93 in forum Excel General
    Replies: 1
    Last Post: 06-19-2020, 03:35 AM
  2. Unique values with Multiple Criteria for Pivoting
    By jjacker in forum Excel General
    Replies: 3
    Last Post: 04-20-2019, 11:29 AM
  3. [SOLVED] get distinct values from criteria AND sum all of the values of the distinct
    By dluhut in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-10-2017, 02:57 PM
  4. [SOLVED] formula to get the average scores per distinct names
    By albert28 in forum Excel General
    Replies: 2
    Last Post: 02-03-2014, 02:33 AM
  5. Formula to Average Days of Distinct Orders
    By tjfulmer1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-12-2013, 02:36 PM
  6. Trying to average a distinct list
    By leftee96 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-07-2013, 04:36 PM
  7. [SOLVED] return average counts for distinct samples but with same name
    By Midna in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-18-2012, 01:08 AM

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