Sum in Pivottable, excluding rows but the same Amount in the Totals

[#1]

Dear Ladies and Gentleman,

I'm looking for a solution for the question following:

(I've been looking around in the Manual and the Community and trying a lot, but didn't solve it)

I want to sum all Revenues And calculate the EBIT. BUT I only want to take the rows into account, where my Revenue is >0.

if([cpa_Net Value]=0,0,Sum([cpa_Net Value]*CurrencyRate)-sum([cpa_Direct Material Cost])-sum([cpa_Material Overhead Cost])-Sum(if(Type='otherCOGS',([cpa_Net Value]*CurrencyRate*Factor),0))-Sum(if(Type='Selling Expenses',([cpa_Net Value]*CurrencyRate*Factor),0)))

This does not work. Although I have the if-statement, I receivefor 0 Revenues a -18k EBIT.

Thank you very much for your help in advance.

[#2]

Please, does anybody has an answer here?

[johnw]

I don't really understand the question. But if you only want selected rows with positive revenue, it might look like this:

if([cpa_Net Value]=0
,0
,sum({<Revenue*={">0"}>} [cpa_Net Value]*CurrencyRate)
-sum({<Revenue*={">0"}>} [cpa_Direct Material Cost])
-sum({<Revenue*={">0"}>} [cpa_Material Overhead Cost])
-sum({<Revenue*={">0"}, Type*={'otherCOGS','Selling Expenses'}
>} [cpa_Net Value]*CurrencyRate*Factor))

[#4]

Dear John,

thank you very much for your answer. Well to be more precise on this.

I want to calculate the EBIT. But the EBIT is always 0 when Revenue is 0.

If I make a Pivot, I does not show me the right numbers

This pivot above is by Country. And if I take a closer look on the single sales-orders:

So, in the Totals a high negative EBIT is calcluated due to 0 Revenues. This should not happen.

I guess my aggr() seems quite not correct. But I don't know how to change appropriate

[johnw · Accepted solution]

I gather you want EBIT for Sweden to be 576 + 492 + 53 + 2775 = 3896? To do sum of rows in a pivot table, where your dimensions are Country and SalesOrder, you'd do this:

sum(aggr(Your EBIT in € expression here, Country, SalesOrder))

To keep the 3896 when removing SalesOrder as a dimension, just leave it in the aggr().

[#6]

Thanks for your reply John.

The thing is, when do the aggr following, it does only show a 0:

sum(aggr(if([cpa_Net Value]<=0,0,
Sum([cpa_Net Value]*CurrencyRate)-
sum([cpa_Direct Material Cost])-
sum([cpa_Material Overhead Cost])-
Sum(if(Type='otherCOGS',([cpa_Net Value]*CurrencyRate*Factor),0))-
Sum(if(Type='Selling Expenses',([cpa_Net Value]*CurrencyRate*Factor),0))))
,[cpa_Main Article Group Description],[cpa_Key Market],[cpa_Country Description])

[johnw]

I don't know.

[#8]

Dear John,

thanks so much for your help. The post I've marked as a verified question does work!! Exactly the [Sales Order] is essential in the dimension of agg().

It was just a mistake somewhere wiht the brakets (). I put the formula step by step toghether.

Once again: THANKS JOHN!!