Pivot total

[Anonymous]

Hi all,

I have a table like below:

Problem is that the totals of Totaal (test) are not shown ... Totaal (test) has the following expression:

[Aantal] * [Kosten]

When selecting for instance only one Type the totals are shown correctly ... but for all Types it is not.

What am I missing here??

thx

[Oleg_Troyansky · Accepted solution]

Most likely, both of your other expressions - "Aatal" and "Kosten", have conditions that work for a single Type but cannot be resolved when multiple types are available.

For example something like this:

If (Type = 'Budget', .... , ....)

THis type of expression can't be resolved at the total level. The solution is to use advanced aggregation and first calculate the values for each Type and then summarize them:

expr. = sum(aggr( <my current expression>, Type))

This should work...

cheers,

[johnw]

Might need to include all of the dimensions? Can't remember for sure. I guess if it doesn't work with just Type, add the others.

sum(aggr(<my current expression>,"Aankoop / Huur","SC","Type"))

[Oleg_Troyansky]

Good point, I missed the other dimensions - you definitely need to list all the dimensions in the AGGR.

[rwunderlich]

Try making your expression aggregatable:

=sum([Aantal] * [Kosten])

-Rob

[Anonymous]

I added this for both Aantal and Kosten ...

sum(aggr(<my current expression>,"Aankoop / Huur","SC","Type"))

Did try some stuff with aggr, but didn't came up this idea so thx.

Just having sum(<Aantal expression>) didn't work ... but with aggr it works great!

Only the problem now is that Kosten is also shown as total but shouldn't be a total ... but already working on that!!

So thanx you both for giving me a clue 😉