I have one data table with various identifiers in 3 columns (Called BU, Company, and Group). I created a cross table that sums the face by 2 layers – an identifier (‘Actual’ and ‘Plan’) and a reporting period (‘9/30/16’ and '9/30/17'). The table was easy, aside from the variance section. I am currently using the formula to compute the variance
SN(Sum([Face]) - Sum([Face]) OVER (ParallelPeriod([Axis.Columns])),
Sum([Face])) AS [PlanVariance]
Unfortunately, this gives me the correct values in the Plan Variance section of the cross table, for the plan identifier. However, it provides the wrong values in the actual identifier. (The actual identifier under plan variance is equal to the actual identifier under the Sum (Face) section. If I remove the SN function, the Plan Variance is empty for all identifiers that have no face for a group AND is empty for the actual section under Plan Variance.
Is there a way to create a cross table that would show the variance for the Plan Identifier ONLY? Can I stop the cross table from calculating the plan variance on the actual segment? Or is there a way to have the actual field hidden in the plan variance section of the final visualization?
Thanks for any help/advice you can provide!