Percentage Calculation wrong in Total Column

[steveniow]

Hi,

I have a pivot table in which I calculate "count({<CurrentIP={'Y'},PathwayRevIdxChange={1}>}DISTINCT Hospid)"and then a % column "count({<CurrentIP={'Y'},PathwayRevIdxChange={1}>}DISTINCT Hospid)/sum(PWCount)"

The percentage given for each individual column is correct but the Total is incorrect. Not sure why I cannot insert a screen shot. Sorry.

Can anyone explain why and how to correct please?

Kind regards

Steve

[steveniow]

Hi,

thanks for taking the time on this. what sample do you need?

the table I have shared shows the EOL row as having a total % of 2.3%. The formula should be calculated using the #/count(PWCount) which from the table is 4/219. however 4/219 is actually 1.8% not 2.3%.

i checked the individual % in the rest of the row and they are all correct - e.g. 1/16 for ALVE is 6.3% and 1/24 for LUCM is 4.2%.

so why does the total column give the wrong % when all the other values in the row are correct?

let me know what you need and I'll send it.

Kind Regards

Steve

[sunny_talwar]

like a qvw file

[steveniow]

Creating a qvw to send you I have found the problem.

the denominator in the % calculation is not what I expected. the sum(PWCount) is not giving me the total pathways I expected - i.e. they they should be all 224 in the totals column

so - how do I get the percentage to provide a denominator which is the total in each column

[sunny_talwar]

May be using TOTAL Qualifier... Sum(TOTAL Measure)

[steveniow]

thank you - however...

now perfect in the total column but applys the same 224  total to each individual ward also, but these should have their own averages/totals...

sorry... trying to build a simple qvd to demonstrate the issue.

[sunny_talwar]

I am not sure I follow... I think a sample qvw will help me understand what you are trying to get

[steveniow]

Hi Sunny,

I have attached a QVW with the same numbers in. I can't send the original as it is too big and contains confidential info but this is doing the same thing.

to be clear. each individual % column is correct. however the Total % column is wrong.

Kind regards

Steve

[sunny_talwar · Accepted solution]

May be this

[#]/sum(TOTAL <WardCode> PWCount)

[steveniow]

You are a Genius - thank you so much.

Not come across that particular form of expression before but has worked like a dream.

Cheers

Steve