Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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().
Please, does anybody has an answer here?
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))
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
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().
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])
I don't know.
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!!