Pivot Table Values As Percentage

[#1]

Hi all,

Please refer to the attached screenshot.

I am trying to work out how I can show the values this pivot table as a percentage of the total row number.
E.g. in the first row, I would like to see value 29/1520, to give 1.9%

That value 29 is an expression setup in the pivot table.

Any help would be much appreciated.

Thanks

[jerem1234]

Depending on your dimensions, you can try something along the lines of:

sum(Sales)/sum(total Sales)

Or if you have a dimension, you can try something like:

sum(Sales)/sum(total <DIM1> Sales)

or

sum(Sales)/sum(total <DIM1, DIM2> Sales)

etc.

Hope this helps!

[Gysbert_Wassenaar]

If your expression is now sum(X) change it to if(dimensionality()=0, sum(X)/sum(total <DimA> X), sum(X)), but replace DimA with the name over which you want the percentage calculated.

[#4]

Here is my expression which calculates the row values. The TOTAL at the end of the row comes from ticking, show partial totals for the expression.

Can anyone tell me how to edit this:

Thanks

Count(if( AssessmentDate >= $(vAssessmentStartDate) AND AssessmentDate <= $(vAssessmentEndDate), MarkRange)))

[jerem1234]

Something like:

Count(if( AssessmentDate >= $(vAssessmentStartDate) AND AssessmentDate <= $(vAssessmentEndDate), MarkRange))/Count(total <DIM1> if( AssessmentDate >= $(vAssessmentStartDate) AND AssessmentDate <= $(vAssessmentEndDate), MarkRange))

Replace DIM1 with whatever dimension you are using.

As Gysbert Wassenaar shows, you'll also want to add the condition of dimensionality() so you have the correct sum for your total as well as your other values.

Hope this helps!

[#6]

Thank Jerem124, however that gives a percentage value of the column total, not the row. I.e. it is taking 29/column total at the bottom, where I am looking for the percentage of the row total.

Thanks

[jerem1234]

Play around with the <DIM1> to try and get the correct value, if you have more than one dimension you may need to add like:

<DIM1, DIM2>

I don't know what dimensions you are using or how many.

[#8]

This is my dimension: Thanks

Count(if( AssessmentDate >= $(vAssessmentStartDate) AND AssessmentDate <= $(vAssessmentEndDate), MarkRange)))

[jerem1234 · Accepted solution]

Oh that's called an Expression, on the Dimension tab will have your list of dimensions. The dimension that's the first column of your chart (maybe its employees or students or person etc.). That's the dimension you want.

If you want either upload a pic of the entire chart or create a sample document that demonstrates the same issue.

[#10]

Thanks.
So I now have the following with my dimension being the <Markrange>.

It works, but it is giving the % value as a % of the Column total, rather than the Row total (if that makes sense).
Thanks

(if( AssessmentDate >= $(vAssessmentStartDate) AND AssessmentDate <= $(vAssessmentEndDate), Centre))/Count(total <MarkRange> if( AssessmentDate >= $(vAssessmentStartDate) AND AssessmentDate <= $(vAssessmentEndDate), MarkRange))