Pivot Table Expression Cascades when grouped

[cbaqir]

I am trying to figure out what is wrong with my syntax that causes the Variance and Forecast Accuracy to cascade to all rows when grouped by Program/Portfolio but if a single Project is selected, it looks correct.

Single and Correct:

Dimensions:

PROG_PORT

REQUEST_ID

Variables and Their Expressions:

vNewForecastAccuracy  =IF(($(vForecastRound)=0 and $(vActualRound)=0),IsNull(Null()),IF(FABS($(vNewVariance))>1,0,1-FABS(($(vNewVariance)))))

vNewVariance  =IF($(vVariance)=0,0,IF($(vForecastRound)=0,IF($(vVariance)<0,-1,1), ($(vVariance)/ ($(vForecastRound)))))

Thanks,

Cassandra

[#2]

hi,

you must remove the sign = in your variables

Variables and Their Expressions:

vNewForecastAccuracy 

IF(($(vForecastRound)=0 and$(vActualRound)=0),IsNull(Null()),IF(FABS($(vNewVariance))>1,0,1-FABS(($(vNewVariance)))))

vNewVariance 

IF($(vVariance)=0,0,IF($(vForecastRound)=0,IF($(vVariance)<0,-1,1), ($(vVariance)/ ($(vForecastRound)))))

[m_woolf]

Here's a guess:

Your variables contain calculations. Those calculations are not done in the chart and therefore are not aggregated by the chart dimensions.

[cbaqir]

Why aren't the calculations done in the chart? It works fine when I select one project.

[m_woolf]

I presume that you have calculations in your variables. Those calculations are not limited by the chart dimensions.

When you select a project, you are limiting the calculation in your variables.

[cbaqir]

So is the answer to not use a variable with calculations in a chart or to add the chart dimensions into the statement? If the latter, how would I do that?

[cbaqir]

Why would that make a difference?

[cbaqir]

It seemed to fix vNewVariance  but not vNewForecastAccuracy.

[#9]

Can you show me the result for vNewForecastAccuracy

[cbaqir]