Calculating Pivot Table values across rows

Question

I have two rows in a Pivot Table that are interdependent on each other. The sum of the two rows cannot exceed 3, while each row cannot exceed 2 (0, 1 or 2).&nbsp; The problem I'm having is I can't figure out how one row can reference the value in another row. Alternatively, if I could access the generated row total (see 6 below), I could then test to see if it exceeds 3 but can't figure out how to access the row total. See example below, John Doe has submitted entries in two categories, Electronic and Print of 4 and 2, respectively. He has submitted a total of 6 entries. His Elec row exceeds the value of 2, which is why he's been flagged with the excessive 2. If I could access his total (6) then I can flag this as exceeding the value of 3. Any help would be greatly appreciated. Thanks in advance.&nbsp;John Doe&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp;Electronic&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp;Print&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp;

riny_van_eekelen · Answer

SteveNBethesda&nbsp;I believe the easiest way to do that is by adding a few columns to the source data for a regular pivot table. Alternatively, use Power Pivot and some DAX measures. Both demonstrated in the attached file.&nbsp;

stevenbethesda · Answer

Hi Riny,I appreciate your prompt response, thank you. Unfortunately, I don't see the attached file. Can you please try again.

stevenbethesda · Answer

Okay, I now see the file, but cannot make sense of the solution.

riny_van_eekelen · Answer

SteveNBethesda&nbsp;Then I may have understood your intentions based on your small example alone. Can you share a file with some realistic data and the result you would like to achieve. Preferable for more than just one individual.Save the file on OneDrive or similar and share a link in your respons.&nbsp;And just to complete the picture, what platform are you on (PC, Mac) and which Excel version do you use? Where does the raw data come from? Can you change it or aren't you able to touch it?

stevenbethesda · Answer

Riny,First and foremost, I appreciate your willingness to help. Thank you.I've attached a copy of our spreadsheet: Competition Submissions Log.v2.Final.2023072 TEST FILE. I know how challenging it is to read someone else's code, so let me explain a little bit.This spreadsheet is for logging competition submissions for my Camera Club. The Coordinator enters data in the "NBCC Competition Log" as he/she receives entries from photographers. The data is summarized in three Pivot Tables on the righthand side. Clicking the "Update Tables" icon refreshes the Tables. Upon refreshing the Tables in the worksheet is locked; there is no password, so to unlock simply select Review &gt; Unprotect Sheet.The Board has recently made changes to the Requirements. Competitors are now permitted to enter a total of 3 entries each month, but no more than 2 in each "Competition Type (Electronic or Print)." The "Competitors Summary Table" shows the number of competitions entered and the total number of submissions for each competitor. If a competitor submits more than 3 entries the "Overage" column highlights the number of excessive entries.I will try to attach the TEST FILE mentioned above, but I don't see a way to attach it. Perhaps after I hit Post I will see an option; otherwise, perhaps you can help.If you need more information, please do not hesitate to contact me. Thanks in advance.

Forum Discussion

Calculating Pivot Table values across rows

7 Replies

Resources