none
Parent/Child Calculation Tabular Model

    Question

  • Hi I have a table in a tabular model where I have created a calculation at the table level and also have a heirarchy of Level_1, Level_2, Level_3

    Total_RES:=(Col_A + Col_B)/ Col_C works fine and returns the total for the table in excel and also works with the created heirarchy.

    I then created a calculated column using the same calc at the row level that returns the value for each record called REC_RES

    I was then attempting to calculate how each REC_RES was when compared to the Total_RES and was trying something like

    =[REC_RES]/[Total_RES].  This just returns a value of 1, I suspect because [Total_RES] is at the D_table level.

    I have attempted a few formulas such as below but just get #Error.

    =CALCULATE ([REC_RES])/SUM(D_Table[Level_1],[Total_RES] .

    Appreciate any hints.

    Wednesday, November 13, 2013 11:58 PM

Answers

  • So instead of using a calculated column I created another measure:

    RPT_RES:=(CALCULATE(SUM([Col_A]), ALLSELECTED()) + CALCULATE(SUM([Col_B]), ALLSELECTED()))/CALCULATE(SUM([Col_C]), ALLSELECTED())

    This is basically creating the Total_RES calculation but is ignoring the Heirarchy so the value for RPT_RES is the same as the report total for Total_RES.  I will now be able to compare the Total_RES to the RPT_RES values.

    • Marked as answer by Binway Thursday, November 14, 2013 3:52 AM
    Thursday, November 14, 2013 3:52 AM