How to avoid negative values (

[gurusai sankar]

Hi All,

Please help me thatHow to avoid negative values totals (1 then Sum(case when [Distinct]=1 then [CAPACITY_FTE] else 0 end) else sum([CAPACITY_FTE]) end) - (case when count([RESOURCE_NAME])>1 then Sum(case when [Distinct]=1 then [ALLOCATION FTE GRP] else 0 end) else sum([ALLOCATION FTE GRP]) end) as [AVAILABILITY FTE]

As pe the sceenshot : i am expecting the grnad total is 2 insted of showing 1.94

 

Kindly help on this.

 

Regards,

guru

[James Watts]

Do you mean you want to replace your SUM([x]) components with

SUM(If([x]

[gurusai sankar]

Hi Jim,Thanks for your quick response. let me explain the scenario with example.Ex: i have a column called Availability FTE. it contains the data in the below. (positive & negative values)

 

Resource project Availability FTE:

A abnc -0.5

B ghj 1.6

C hjii 1.4

 

Grand Total ------------------------ 2.50 (1.4+1.6-o.5 = 2.50)

 

but as per my requirement-----negative values should show in the list but for the grand total- it should be calculate only positive values (ie 1.6 + 1.4 = 3.0 ). so for this scenario grand total should be 3.0. (note : for individual - we can show negative values in the total, only for grand total- we should avoid negative values to sum up, but e have to show negative values also in the list of the table.

 

hope it helps.

kindly let me know if need any additional information.

 

thanks in advance,

guru

[James Watts]

No, the Grand Total line is fixed functionality - it sums the values in the column.  You might want to split it into two cross tables, one for the row values and one for the sum.