Custom Calculation as a row with different Calculations per collum

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) else
if 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. 

Appreciate your help! 

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)