I have created a pivottable to show which donaters donated for this years campaign but also which donaters donated for this campaign last year.
I wanted to have an overview of the number of donaters that donated both campaigns. I therefore added a formula resulting in a yes or no value for each donatorsid. I counted the 'yes' values: 549.
I wanted to have this data in the original data (I added the data in the powerpivot datamodel) and added a column in the original table. I then used vlookup to find the corresponding value for each donatorsid from the first pivottable.
So far so good. Now I created a new pivottable from the original data counting the yes and the now values and now I have 551 'yes' values.
I cannot think of any reason why these numbers are not equal. I already thought about some donators being multiple times in the list (every donation is in the list separately, so donators who donated more than 1 time are multiple times in the list with donations).
But I had this figured and have every unique donatorID in the pivottable and for the second pivottable, used the distinct count. But somehow, I resulted in a difference of 2. I will upload the sample file.
sample file: https://file.io/DQeZ1wsOKJqn
on the worksheet "donateurs" the results is 549. On the sheet "Blad2" the result is 551.
edit:
I noticed that for both of the different types of donators (contact and account) the value is 1 higher adding up to the difference of 2. So for contact the value is 541 instead of 540 and for accounts the value is 10 instead of 9. I know that when adding a pivottable in a worksheet and in the wizard checking the 'add to datamodel' checkbox causes empty lines to occur in pivots / the powerpivot datamodel. But so far, that is not what happens here. I first created the powerpivot datamodel and from the powerpivot datamodel I created the pivottable to the worksheet.
When diving somewhat deeper into this, I found out that the unique count actually does not a unique count?
When double clicking on the 10 in the table below:
These are the rows I get in the list:
As you can see, there are 2 values double in the list. When I create a distinct count from these I get the original 9:
I still don't get why I get different numbers in the beginning.