Totals don't add up in Pivot Table

[#1]

Please take a look at this pivot table. The individual numbers are correct. But the Totals do not add up. The formula I have for calculating individual numbers is Count(Distinct upper([Unique Report])). How do I get the totals to agree? I have also attached the excel version.

stalwar1‌ can you help?

[sunny_talwar]

Script:

Table:

LOAD Date(MonthStart([Approved Date]), 'MMM-YY') as MonthYear,

    If(Days < 16 or IsNull(Days),' Within Policy',

    If(Days < 31, '16-30 Days',

    If(Days <61, '31-60 Days',

    If(Days < 91, '61-90 Days', '90+ Days' )))) as Bucket,

    [Unique Report];

LOAD * INLINE [

Approved Date, Days, Unique Report

10/6/2015, 2, abcdef

10/28/2015, 18, abfrgd

4/28/2016, 25, okyhfs

10/28/2015, 20, abcdef

10/28/2015, 20, abcdeh

];

Dim:

LOAD * INLINE [

Dim

1

2

];

Dim2:

LOAD * INLINE [

Dim2

1

2

];

Dimensions

1) =Pick(Dim, MonthName(MonthYear), 'TOTAL')

2) =Pick(Dim2, Bucket, 'TOTAL')

Expression

If(Dim = 1 and Dim2 = 1, Count(Distinct Upper([Unique Report])), Count(Upper([Unique Report])))

Also, you will need a sort expression

Dimension1 -> Dim

Dimension2 -> Dim2

[sunny_talwar]

Script looks good to me. How does your dimension and expression look like?

[#4]

This is row dimension

Pick(Dim, MonthName(Months), 'Totals')

This is column dimension

Pick(Dim2, Aging, 'TOTAL')

This is measures

if(Isnull(Sum(Aggr(Count(Distinct Upper([Unique Report])), Aging,Months))),0,Sum(Aggr(Count(Distinct Upper([Unique Report])), Aging,Months)))

[sunny_talwar]

How about this as the expression:

If(Dim = 1 and Dim2 = 1,

if(Isnull(Sum(Aggr(Count(Distinct Upper([Unique Report])), Aging,Months))),0,Sum(Aggr(Count(Distinct Upper([Unique Report])), Aging,Months))),

Count(Upper([Unique Report])))

[#6]

When I use that expression, I get a total that is about 10 times what it should be.

[sunny_talwar]

How about this:

If(Dim = 1 and Dim2 = 1,

if(Isnull(Sum(Aggr(Count(Distinct Upper([Unique Report])), Aging,Months))),0,Sum(Aggr(Count(Distinct Upper([Unique Report])), Aging,Months))),

Count(DISTINCT Upper([Unique Report])))

Right now its all guess work as I don't have your actual scenario to look at.

[#8]

This is progress as I am getting a number that is closer. It is the sum total where it is not making a distinction between the change in the "months" or "Aging"

[sunny_talwar]

It is a sum total.... not sure I understand... can you explain with numbers

[#10]

Look at my original post with numbers. I am getting exactly those numbers now. We were able to solve that issue with your solution. Everything was ok but I needed the totals at the bottom. So we took a slightly different approach. And the good thing is that I am able to get the total to the bottom. But the numbers need to be fixed.

[#11]

We are almost there stalwar1‌

Don't give up on me now.

Thanks for all your help so far.