CODEWITHSUNDEEP
spotfire
Max S.
Max S.

Reputation: 17

In a separate column, how can I show the difference of a percent of total custom expression?

I have two environments, A and B, in a CROSS TABLE. Each environment has stores with the amount of units next to them. Additionally, there is a column that shows the percentage of total units for each store in each environment.

The code for percentage of total is as follows:

Sum([UNITS]) THEN [Value] / Sum([Value]) OVER (All([Axis.Rows])) as [% Units]

Let's say store 1 has a different percentage of total for each environment. I want to create a separate custom expression that shows the difference between these two percentages.

Right now, I have a variation of this that is not desirable. It simply shows the percent change in units for store 1, rather than the change in the percentage of total. This code looks like:

(Sum([UNITS]) - Sum([UNITS]) OVER (PreviousPeriod([Axis.Columns]))) / Sum([UNITS]) OVER (PreviousPeriod([Axis.Columns])) as [Unit Difference]

I have tried unsuccessfully to embed the first piece of code within the second piece. Any help will be greatly appreciated!

Upvotes: 0

Views: 70

Answers (1)

Chelsea
Chelsea

Reputation: 511

I believe what you're looking for is something along the lines of

SUM(If([Environment] = 'A', [Units], 0))/(SUM(If([Environment] = 'A', [Units], 0)) OVER (Parent([Axis.Rows])) - SUM(If([Environment] = 'B', [Units], 0))/(SUM(If([Environment] = 'B', [Units], 0)) OVER (Parent([Axis.Rows])) as [% Difference]

This would require removing the A/B differentiation from the Horizontal access and replacing it with (Column Names) and making two different columns of custom expressions, composed of 

SUM(If([Environment] = 'A', [Units], 0))/(SUM(If([Environment] = 'A', [Units], 0)) OVER (Parent([Axis.Rows]))  as [A %]

and 

SUM(If([Environment] = 'B', [Units], 0))/(SUM(If([Environment] = 'B', [Units], 0)) OVER (Parent([Axis.Rows]))  as [B%]

If this is not what you are looking for, I suggest you clarify with an example with example numbers showing what you want the output to look like.

Upvotes: 0

Related Questions