Forum Discussion

CAOK's avatar
CAOK
New Contributor II
2 years ago

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

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?

Cube View
  • aricgresko's avatar
    aricgresko
    2 years ago

    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.

  • JackLacava's avatar
    JackLacava
    Honored Contributor
    2 years ago

    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's avatar
      CAOK
      New Contributor II
      2 years ago

      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)

      • aricgresko's avatar
        aricgresko
        Contributor III
        2 years ago

        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's avatar
    aricgresko
    Contributor III
    2 years ago

    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's avatar
      CAOK
      New Contributor II
      2 years ago

      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?

      • aricgresko's avatar
        aricgresko
        Contributor III
        2 years ago

        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.