Pivot Table totals are wrong

[#1]

Im trying to create a fairly simple table which based on the dimension "Income/Cost", and 3 expressions  "YTD income", "YTD cost", "Net". This works for each row, but the table totals arent right (its adding the costs & income together). An example of the result is below:

Activity     Income/Cost  YTD Income   YTD Cost      Net

AAEM01   Income          £28,621          0                  £28,621

                Cost               £0                    £65,284     -£65,284

TOTAL                          £93,905            £93,905     £0

The expression to calculate YTD income is:

If(Income/Cost = 'Cost',0,sum( [YTD Total] ))

Ultimately, I want to remove the Income/Cost dimension to just show a total for each activity.

Any thoughts/solutions much appreciated.

Many thanks in advance

[Anonymous]

try using

if (Income/Cost='Cost',sum(total <Income/Cost> [YTD Total])

how is the definition of [YTD Total]?

[#3]

Thanks for the quick reply.

The total has changed, but to £0

YTD Total is my import data by cost code, e.g. staff cost, travel, accomodation, printing, phones, etc etc

[Anonymous]

sorry, thats true, in the total row there is no income/cost

if you remove the Income/Cost column you won't get the result as stated in your desired table

alternatively you can use an own column with sum(YTD Income)+sum(YTD Cost) and you have the total number

[#5]

Try the following:

1 Creates a pivot table.

2 In the expression stated

        sum ([YTD Total])

3 in one dimension adds the column "Income / Cost"

4 Add the dimension visually on the expression in the table, as shown in the picture

5. tab filing, adds the option to display totals

[vijay_iitkgp · Accepted solution]

I think if Income / Cost is associated with Data then you can simply use the expression:

Sum(If(Income/Cost = 'Cost',0,( [YTD Total] )))  for Income

[#7]

Thanks, yes this works

[#8]

Thank you