Pivot Table wrong total | Problem with expression

[dominikkeller]

Hey
i have a problem with my expression.

I build an overview of who has lent whom for how many hours. This is used for internal billing.

However, I have an exception. If the lender and recipient have a certain constellation, the wage factor is at 13.5, otherwise at 18. In the picture you can see that my total is incorrect. I just don't come up with the solution in my formula.

if( /*Lender*/AbrechnungsDISPLAYNAME='MA Oldenburg' and /*Reciever*/ArbeitsDISPLAYNAME='CBC Oldenburg',

  (ceil((sum({$</*Dismiss Working Area */KLV_Master.datev_nr-={$(=1015)}>}/* Working Seconds */KLV_Master.gesamt_std_in_sek)/60/60),0.25)*/* Wage factor */13.5),

  (ceil((sum({$</*Dismiss Working Area */KLV_Master.datev_nr-={$(=1015)}>}/* Working Seconds */KLV_Master.gesamt_std_in_sek)/60/60),0.25)*/* Wage factor */18)) 

The images:

1: The version i need for my further calc. 

2: Just to show my Problem

Any Ideas?

[marcus_sommer · Accepted solution]

Your expression is not just a straight aggregation else some kind of rate with conditions. The dimensionality on which your conditions are based didn't exists / aren't properly applied in the total-calculation and therefore the total didn't show the sum of the displayed rows. To get this result you need to wrap your expression within an aggr() like:

sum(aggr(YourExpression, Dim1, Dim2))

whereby Dim1 und Dim2 are placeholder for the dimensional context on which the calculation should happens - usually it are the dimensions within your chart like Lender, Arbeits... but you may need further dimensions like period-fields or similar if they are essential in the calculation-context.

- Marcus

[dominikkeller]

Thank you! 

It worked perfect with the Dim's Lender and Reciever.