Calculation across rows

[#1]

Hi guys,

I'm looking to a better way to implement the calculation below.

What I need to calculate is the value in the last columns below.

Dimension 1	Dimension 2	Value	% of A
X	A	100	100%
X	B	50	50%
X	C	30	30%
Y	A	200	100%
Y	B	50	25%
Y	C	40	20%

The last column show the % of the value in the specific row compared to the value where dimension 2 is equal to 'A'.

I'm having issue calculating the denominator of my formula that should always be equal to the value measure when dimension 2 is  equal to A.

If I specify the denominator through Set Analysis as

SUM({<[Dimension 2]={'Gross Sales'}>} Value) I got the below

Dimension 1	Dimension 2	Value	Denominator
X	A	100	100
X	B	50
X	C	30
Y	A	200	200
Y	B	50
Y	C	40

While I need to have 100 and 200 repeated on all rows.

If I use Total as SUM({Total<[Dimension 2]={'Gross Sales'}>} Value) I got the below

Dimension 1	Dimension 2	Value	Denominator
X	A	100	300
X	B	50	300
X	C	30	300
Y	A	200	300
Y	B	50	300
Y	C	40	300

That is again wrong as  the total is done ignoring all dimension grouping.

I got it to work specifying the group by dimension for the total as follow SUM({<[Dimension 2]={'Gross Sales'}>} Total<[Dimension 1] Value) but i would  like to know if:

- There's any better way to express this as right now in my total I need to list all the dimensions excluding the one on which I'm applying the filter (Dimension 2 in the example)

- In case of many dimensions to group on and high volume of data there may be performance implications

Thanks

Andrea

[annafuksa1]

Can you post app ?

[rubenmarin]

Hi Andrea, I attach an example using above function, It will get the first row based on the last dimension, so if there is a Dimension after 'Dimension 2' probably won't meet your requirements.

[#4]

Thanks, but I fear is not going to work for more complex scenarios.

I created a small example but I'm not sure how I can upload it here.

Thanks

Andrea ! !

[rubenmarin]

When you're  answering, in the upper-right corner there is a link to advanzed editor:

From the you can upload files:

[#6]

Attached I created a small example.

In short I want to understand if there's a better way to calculate the denominator than the one I used in the "Denominator - Total Group By" expression as I don't like the idea of listing all my dimensions into the Total function.

Thanks

Andrea

[rubenmarin]

Hi Andrea, different approaches:

- the above function i told you before

- modification in script so there is the TOTAL value in each record

- using a variable to list the dimensions, playing with the value the variable should return you can work to make this more dynamic. (using concat if there is a field where user selects dimensions)

Hope any of this helps you.

[#8]

Thanks a lot Ruben, for providing some options.

I'll probably end up adding the data column to the script and use that in my calculation.

Andrea