Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I need to calculate a percentage in a column:
Column(1) / Sum(Total <Id> Sales),
but the percentage has an error, because instead to use 34700, the value use it's 29629 (=34700 - 1538 - 3533), 1538 and 3533 are repeat (colour red and blue). but, I don't know. why happened with it? Thank you.
Id | Sales | % Correct using 34700 | % Wrong using 29629 |
Mark1 | 1538 | 4,43% | 5,19% |
Mark1 | 5177 | 14,92% | 17,47% |
Mark1 | 2577 | 7,43% | 8,70% |
Mark1 | 3533 | 10,18% | 11,92% |
Mark1 | 210 | 0,61% | 0,71% |
Mark1 | 3894 | 11,22% | 13,14% |
Mark1 | 130 | 0,37% | 0,44% |
Mark1 | 172 | 0,50% | 0,58% |
Mark1 | 1538 | 4,43% | 5,19% |
Mark1 | 3533 | 10,18% | 11,92% |
Mark1 | 9200 | 26,51% | 31,05% |
Mark1 | 3198 | 9,22% | 10,79% |
Sum OK | 34700 | ||
Sum Wrong | 29629 |
Hi
If this is a straight table, select sum of rows for the % expression.
If this is a pivot: Use this aggr expression to get the total of the percentages (as opose
Sum(Aggr(Sales / Sum(Total <Id> Sales), Id, <other dimensions>))
<other dimensions must include all the table dimensions - so if Date and Customer are other dimensions:
Sum(Aggr(Sales / Sum(Total <Id> Sales), Id, Date, Customer))
That will give you the sum of the %ages.
HTH
Jonathan
PS I am assuming that the expression for Column(1) is Sum(Sales)
Check enclosed file...