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 :) :)
1
Comments
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:
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:
m4:
Index:
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:
Please sign in to leave a comment.