Challenge in Pivot table

[qv_testing]

Hi Team,

I have different scenario,  trying since one week but I couldn't achieve.

Experts can you please help me!

By default, pivot table is working fine,  calculation is ([Amount (USD)]*[Gross BPS])/12*0.5

but my requirement is - let say for Cashflow 5 - May2021 value is 4453  that's fine 

but my greater than month May, calculation should be ([Amount (USD)]*[Gross BPS])/12

Screen 1: default pivot table

Screen 2: below screen is my out put

Hope you understand and Thanks in Advance!

[Kushal_Chawda · Accepted solution]

@qv_testing  try below 

=if(Before(total Column(1))>0, 
sum(total <Client>[Amount (USD)])*sum(total <Client>[Gross BPS])/12,
sum([Amount (USD)])*sum([Gross BPS])/12*0.5)

[qv_testing]

Perfect 😊 - Thank you Kushal

[qv_testing]

Hi Kushal,

without any selection working fine, but when user selections values are not showing correct and Months not showing Jan-Dec. few of them months are missing so values mismatching.

and months also not showing all, may be if months show all values will be correct.

can you please help

[Kushal_Chawda]

@qv_testing  I am not able to open the qvw as running personal edition. What expression are you using?

[qv_testing]

Same expression which you were shared 

If(Before(total fabs(Column(1)))>0, 
sum(total <Client>[Amount (USD)])*sum(total <Client>[Gross BPS])/12,
sum([Amount (USD)])*sum([Gross BPS])/12*0.5)

I have some negative data also, so I have included fabs function.

@Kushal_Chawda , can you please have a look attached original data

PFA,