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.

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!

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?