Display on pivot table a count of only those column values which has a unique ID (in a separate column)

Karth3535

New Member
Joined
Jun 29, 2020
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
Explanation of my source table: I have column E which has data-validation dropdowns for Reviewer numbers, and column R which has traffic transaction details. Reviewer now check each of the transaction (Column R), and once review is done, will click on the dropdown (Column E) and select Reviewer# 1, to indicate that the review is completed.
123.PNG
Issue.gif

In a separate tab, I have the pivot table which has a Power Pivot measure to count the number of items reviewed by the Reviewer; =COUNT(Ops_Table[Image Reviewer's Name]). This works great and displays the value of all the transactions which were reviewed.
456.PNG

Where I am facing trouble:

Sometimes Column R can have duplicate values, which means that same vehicle has transmitted redundant data, for example in the attached picture, Veh Trx ID - 270871136 was displayed thrice. When redundant trx ID were created, reviewer will mark his/ her name in Column E to indicate that these trx were reviewed, but since it is the same Transaction, we want to count it only once, and not thrice.

If it were just a table, i could have used COUNTIFS, but since it is a pivot table wherein I need to display my count, I am having an additional layer of ambiguity, since when I create a measure, I observed it does not support COUNTIFS.

Can somebody please help in providing ideas on how should i display the "Count Items in Column E while making sure that Column R Trx ID is unique and not repeating." Please let me know if you need me to further explain the problem. Thanks a lot in advance.

Please note: I cannot be using VBA, so please provide me with a non-VBA response. Thanks.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Forum statistics

Threads
1,141,301
Messages
5,705,583
Members
421,400
Latest member
chakam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top