Cube-view: Calculate a column based on a calculated row

CAOK
New Contributor II

My Cube view has entities in rows. One of the rows is a sum of several entities ( E#A1+E#A2+E#A3). In the 4 columns I have Actual, Budget, Variance and Variance%. Variance and Variance % being calculated from the two first columns. Works fine except for the row that is a sum of several entities. For this row I believe the variance-columns displays the variance for the first entity (E#A1). How can I solve this? Can I set calc order anywhere? CubeView.PNG

1 ACCEPTED SOLUTION

Use Row Overrides on your two columns (Var mkr and Var %).  You should be able to just copy the exact Member Filter you have in Member Expansion 1 and paste that into the Row Overrides below.  Then just put the name of the row(s) you need to override in the Row Range.

View solution in original post

7 REPLIES 7

JackLacava
Community Manager
Community Manager

Hi CAroline,

Can you post the definition of that column, please...? In theory, if you're referring to column values through CVC, it should reference the summed value.

CAOK
New Contributor II

In theory yes, but I don't think it references the summed value - I have had problems with this before. The definitions are for column 3: GetDataCell(CVC(actual)-CVC(budget)):Name(Var mkr)  and for column 4: GetDataCell(VariancePercent(CVC(actual), CVC(budget))):Name(Variance %) It works for all rows escept for the row that instead of one Entitymember has the definition GetDataCell("E#DK+E#FR+E#SF"):Name(Other Entities)

Hi - look at my comment below from yesterday.  Your response to Jack confirms my thought of needing an override.  Basically, you have two GetDataCell functions intersecting with each other.  The override should resolve your issue. 

Let me know if that does the trick!

aricgresko
Contributor III

The variance column looks incorrect (Var mkr showing -27).  This typically means that you need to use a column or row override in the report.

CAOK
New Contributor II

OK, what would that column override look like? And in that case I suppose I could not combine column and row templates because I would have to hard-code the override?

Use Row Overrides on your two columns (Var mkr and Var %).  You should be able to just copy the exact Member Filter you have in Member Expansion 1 and paste that into the Row Overrides below.  Then just put the name of the row(s) you need to override in the Row Range.

CAOK
New Contributor II

Thanks! It works - not sure why though, is it that by setting it as an override I set the calc order in the CV so that these columns are calculated after the rows?