Custom Calculation as a row with different Calculations per collum

Ulrikke Jørgensen

April 20, 2023 09:01

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 :) :)

Comments

5 comments

Ole Dyring
April 20, 2023 11:33
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
0
Ulrikke Jørgensen
April 21, 2023 13:05

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!

0
Ole Dyring
April 24, 2023 06:58
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
0
Ulrikke Jørgensen
April 24, 2023 08:46

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?

0

Ole Dyring

April 24, 2023 10:25

Yes.

E.g. m4:

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

Please sign in to leave a comment.

Didn't find what you were looking for?

New post