Subtotal doesnt match the rows it's totalling?!

[BH1983]

Hi all, I have a power pivot which is showing the distinct count of a set of data. I have subtotals applied but they don't total the row beneath it.

I've attached an example showing a subtotal of 1,418, but if you add up the rows beneath it they total 1,730 - why would it be different??
TIA!

[Glenn Kennedy]

Can't tell much from a picture!! Please post an Excel file.

[BH1983]

Can't tell much from a picture!! Please post an Excel file.

I can't, the data sitting behind it is P&C company data.

[Glenn Kennedy]

Delete the stuff that's not needed to demonstrate the issue.

[BH1983]

Delete the stuff that's not needed to demonstrate the issue.

Ok here it is with everything but the fields making up the totals.

[Glenn Kennedy]

I know what's wrong... but. The Pivot column subtotals are counting DISTINCT policy numbers. There are 1417 of them (after you refresh the Pivot Table). However, the row-by-row list of lines is counting ALL of them... not the distinct ones.

The BUT is, that using a Pivot Table, I'm not entirely sure how to fix it!! Personally, I hate Pivot Tables and would use a formula to deliver the desired results.

What VERSION of O365 are you using?

[BH1983]

I know what's wrong... but. The Pivot column subtotals are counting DISTINCT policy numbers. There are 1417 of them (after you refresh the Pivot Table). However, the row-by-row list of lines is counting ALL of them... not the distinct ones.

The BUT is, that using a Pivot Table, I'm not entirely sure how to fix it!! Personally, I hate Pivot Tables and would use a formula to deliver the desired results.

What VERSION of O365 are you using?

I don't think that's it though if you look at the data befind "SUR" for example 19 rows but only 7 distinct policy numbers which is what's shown on the pivot. I'm comfortable with what's shown at the line level, but I can't work out how it's calculating the subtotal of 1,417?

[Glenn Kennedy]

Here's where 1417 comes from:

=ROWS(UNIQUE(FILTER(Table1[Policy Number],(Table1[Country]="France")*(TEXT(Table1[Renewal Date],"mmm")="Jan"))))

It selects January from column D,
ONLY for France
returns all the policy numbers
Then returns only the unique onces
then tells you their number (in effect... the number of rows occupied by that subset).

[josephteh]

Create a helper column in your Booking Tracker Table with a formula like =IF(COUNTIF(E$2:E2,E2)=1,1,0), then sum this column in Values field of your PivotTable.

[Glenn Kennedy]

I don't think that's it though if you look at the data befind "SUR" for example 19 rows but only 7 distinct policy numbers which is what's shown on the pivot. I'm comfortable with what's shown at the line level, but I can't work out how it's calculating the subtotal of 1,417?

OK... yes, I see that now.

[BH1983]

OK... yes, I see that now.

And this is where I'm confused, how can the individiaul rows in the pivot total 1,729 unique policies but the subtotal show 1,417?

[Glenn Kennedy]

Right.... Check Policy 0177538

It is listed as:

TECH
FINANCIAL
MARINE
CASUALTY
FIRE

So that ONE distince policy is being counted 5 times in the category listing. That's your problem.

[BH1983]

Right.... Check Policy 0177538

It is listed as:

TECH
FINANCIAL
MARINE
CASUALTY
FIRE

So that ONE distince policy is being counted 5 times in the category listing. That's your problem.

Ah! so it's counting it once at sub total level, but counted in each of the categories listed below it?

[Glenn Kennedy]

Yep. That's it in a nutshell. You need to think about WHAT totals you actually need.

If you need more help, shout. If not...

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.