Sum of rows in pivot table

[hopkinsc]

Hi all,

I have an expression in a pivot table, its a simple IF() statement,

if(column(1) >0 and Column(2)=0,1,0). 

The expression is giving me what i want but i am trying to get a total at the top which i am struggling to do, 

I have tried using SUM(AGGR()) around it over the dimensions but it doesnt work. 

Is anyone able to take a look please?

I attached a QVW. 

Many thanks in advance. 

[Arthur_Fong]

Try this for the expression in EXP1:

if(Dimensionality()=0,
count(distinct [Order Number]),
if(([CA Invoiced]>0 and [CA Cancelled]=0),count(distinct [Order Number]),0)
)

Thanks and regards,

Arthur Fong

[hopkinsc]

Hi, thanks for your reply.

That solution gives me a total but its the wrong total as a lot of the rows are now incorrect. 

i.e.

The highlighted rows should be 1 as there is a value in 'CA Invoiced' and CA Cancelled is 0. 

The total i am looking for should be 2743. 

[StarinieriG · Accepted solution]

Hi,

try to use this: 

If(Dimensionality()=0,Sum({<[Revision Reason]=>}Aggr(if(SUM({<[Revision Reason]=>}[Quantity Invoiced - Cases])>0 and SUM ([Quantity Canceled - Cases])=0,1,0), [Order Number])),
(if([CA Invoiced]>0 and [CA Cancelled]=0,1,0)))

[hopkinsc]

Thats great, thanks very much! 

[agigliotti]

maybe this:

=sum( aggr( if( SUM({<[Revision Reason]=>}[Quantity Invoiced - Cases])>0 and SUM ([Quantity Canceled - Cases])=0, 1, 0 ), [Order Number] ) )