Total row in pivot table with null value

[DNeri]

Hi,

I have an expression as follow:

=if(dim=a, 123*sum(time), if(dim=b, 234*sum(time), if(dim=c, 345*sum(time)))).

I understand that for the total, it cannot calculate the expression due to the fact that there is not a specific dimension, but is there a way around?

Thank you!

[Vegar]

You could change the total from expression total to sum of rows.

Or maybe it can be solved  by changing to this expression.

Pick(Match(dim,a,b,c) 123, 234, 345) * sum(time)

[DNeri]

Thank you Vegar for your quick response.

I cannot change the total from expression to total of rows, because that option in my case is greyed out.

I thank you for teaching me this new function (Pick and Match) instead of multiple IF statements, I do prefer it.

Unfortunately, in the Total is still showing Null as value.

[Vegar · Accepted solution]

Take a look at the attached qvw file. I've created an example where I have two working expressions. One using pick match and one using your nested if expression. (I made some adjustments to my previous expression like adding ' ' and moving the sum to include everything. I also added an aggr to your IFs)

Pick(Match())
=sum(Pick(Match(dim,'a','b','c') ,123, 234, 345) * time)

Nested IFs
=sum(aggr(if(dim='a', 123*sum(time), if(dim='b', 234*sum(time), if(dim='c', 345*sum(time)))), dim))

[DNeri]

Thank you Vegar,

your suggestions work perfectly.

Thank you so so much!