# Custom Calculation as a row with different Calculations per collum

Hi Clever People,

I have a vary specifik table, that i need som help to achive (if even posible)

The table i want to create looks like this:

 Region Country Days Invoiced Index Nordic DK Actual data Actual data Calculation SE Actual data Actual data Calculation NO Actual data Actual data Calculation Sum of rest Average Sum Calculation Europe Spain Actual data Actual data Calculation Italy Actual data Actual data Calculation Sum of rest Average Sum Calculation Asia Malaysia Actual data Actual data Calculation China Actual data Actual data Calculation India Actual data Actual data Calculation Sum of rest Average Sum Calculation

So the article about a top list with a sum of the rest helped me along way.

However, now i would like to modify my row with the sum of the rest to not only contains sum, but also avg and calculations for specific collums. Next i would like to recreate that row for every specifik customer region.

Would this in any way be posible?

Thank you in advance :) :)

• Hi Ulrikke,

When you have multiple measures and you want your calculation to behave differently for each measure, you will have to do something like this in a calculated row:

`if allcount(d1, d1, m1:0) = 1 then avg(0, all, 0) elseif allcount(d1, d1, m1:0) = 2 then sum(0, all, 0) else 0`

For the other question, to do this calculation for every specific customer region ... For this work, you will probably need to implement this as a calculation that affects the standard subtotals. Maybe this is what you already did in your example... not sure?

Let me know how it goes with above. Provide more details if you need further help.

BR / Ole

• Hi Ole,

Thank you so much for your quick respons, det calculation you send worked perfektly!

Yes, i would like to modify the standard subtotals, but i am not sure how to do that. I don't fint the calculation for the standart subtotal anywhere where i can modify it.

I have triede something like this:

sum(0, @"[Middle Europe & CIS]"(h), 0)

But it comes out undefined. I would also very much like these subtotal to lie under each region in the tabel.

• Hi Ulrikke,

All right. Here comes another suggestion.

Note that my example has been designed for a 2-level hierarchy on the vertical axis.

First, the resulting output:

Notice that I have enabled 'Column totals after members':

'Revenue' and 'Profit' are my two original measures (similar to 'Days' and 'Invoiced' in your example).

I have furthermore added three calculations to the crosstab. All three have been added 'As a new measure'.

m3:

`if allcount(d1, all(c), m1) = 0 then sum(d1, 0, m1) else avg(d1, all(v,c), m1)`

m4:

`if allcount(d1, all(c), m1) = 0 then sum(d1, 0, m2) else sum(d1, all(v,c), m1)`

Index:

`sum(d1, 0, m4) / sum(d1, 0, m3) * 100`

I don't know if this is how your Index should be calculated, so I just added something here.

The original measures 'Revenue' and 'Profit' can be hidden by the Visibility settings, and the m3 and m4 calculations, which is probably replacing the original  measures can be renamed to something more useful.

BR / Ole

• Hi Ole,

But can i modify the subtotal to only calculate for the hidden members? To work as a sum of the rest for each region?

• Yes.

E.g. m4:

`if allcount(d1, all(c), m1) = 0 then sum(d1, 0, m2) else sum(d1, all(h,c), m1)`