Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a table with a number of Country as a dimension.
So for example the table contain, 1 dimension, with values of:
China 100
SA 10
India 20
Australia 100
US 60
UK 80
At the bottom, I'd like to add in a dummy/fake or adhoc row called Intra Country.
This value is not in the data, but is basically the sum of rows total of the table MINUS expression total.
So perhaps the sum of rows total = 500
Expression total = 370
Intra Country= 500-370 = 130
I want to achieve something that looks like this, with the Intra Country line at the bottom.
Intra Country 130
I'm finding it tricky to to find a way of getting this 'Intra Country' dimension value at the bottom.
I'm sure there is a function I could use to create a calculated dimension?
Any ideas would be much appreciated
Thank you all
Hi
Is the total sum 500 static or is user defined at dashboard ?
Suppose we select China and SA, so what should be the value of Intra Country ?
Provide data please,, I would like to work on this...
One of the solutions could be adding (already in the model) this additional value - Intra Country, with null value:
Which would look like this:
load * inline [
Country, Value
China ,100
SA,10
India, 20
Australia,100
US,60
UK,80
Intra Country
]
;
and then in the straight table instead of using the sum use proper if function:
if(Country='Intra Country',sum({1} total Value)-sum({$} total Value), sum(Value))
