In Pivot table, How to fabs first and then sum

[Anonymous]

Hi,     I’m trying to get the final result as sum of total for the +ve and –ve values. Please help.sunindia‌

[sunny_talwar]

If you make it positive, I get a little different number using this:

If(Dimensionality() = 2, SUM([TOTAL EXPENSES]), Sum(fabs([TOTAL EXPENSES])))

[sunny_talwar]

The answers matches your if we completely exclude negative from the calculation

If(Dimensionality() = 2, SUM([TOTAL EXPENSES]), Sum({<[TOTAL EXPENSES] = {'>0'}>}[TOTAL EXPENSES]))

[ramasaisaksoft]

Hi Kumar

check the link -ve to +ve convertion

*** Negative Value to Positive Value ***

[swuehl]

If you are trying to show the sum-of-rows (with absolute values) for the partial sums, but still also negative values for the details, I would suggest that you create a field in your data model

LOAD

     ...

     if( left([VENDOR ID],3)= 'CPM' and Risk_Type = 'RISK', 'CAC_RISK', Risk_Type) as New_Risk_Type,

     ...

Then in your chart, use this new field as dimension and as expressions

=If(Dimensionality()<2,

Sum(Aggr(

Fabs(SUM( [TOTAL EXPENSES]))

,MARKET,New_Risk_Type))

,

SUM([TOTAL EXPENSES]))

[Anonymous]

‌Bro.. Can you check the total for orlando. It should be 73,743,387 when we exclude the negative value completely...

[Anonymous]

‌Bro.. Can you check the total for orlando. It should be 73,743,387 when we exclude the negative value completely... Please help...

[Anonymous]

hi swehul,

s‌till I'm getting the wrong total.

[swuehl]

Could you upload the changed QVW with the new dimension and could you also detail what you expect to see, i.e. which lines should show which values and how you derive the values from your original table?

[Anonymous]

‌As per my requirement, it should exclude the negative value from the total.