sum of calculated field

[rocku2008_1985]

I have created a calculated field 9  as 

calculatefield9 = DIVIDE(AVERAGE('AIS_New'[AISHours]),SUM('AISLocation_new'[HoursPerDay]) * MAX(AIS_New[ChairNumber])* max(BusinessDays[BusinessDays])). This gives me the percentage value for each row. I want to get the total sum of the calculated field9. So that I get the percentage total. I tried to use calculatedfield11 = Sum ('AIS_new'[CalculatedField9]). It is not showing CalculatedFiled9 in AIS_new dataset even if it has it. So, I created another caculated field as 

CalculatedField10 = SUMX(ALL('AIS_New'),DIVIDE(AVERAGE('AIS_New'[AISHours]),SUM('AISLocation_new'[HoursPerDay]) * MAX('AIS_New'[ChairNumber]) * MAX('BusinessDays'[BusinessDays]))). This is giving me the total percentage value as 37.02% but I should be getting as 15.6% on adding all the percentage values which are (

0.46%
0.47%
0.13%
0.29%
0.54%
0.55%
0.49%
0.37%
0.54%
0.66%
0.45%
0.72%
0.65%
0.27%
0.44%
0.29%
0.40%
0.62%
0.35%
0.32%
0.90%
0.75%
0.58%
0.76%
0.71%
0.45%
0.47%
1.19%
0.53%

 Please help me out. I am stuck in this for hours.

[v-yanjiang-msft · Accepted solution]

Hi @rocku2008_1985 ,

According to your description, I think the reason of error is in the total row, MAX('AIS_New'[ChairNumber]) and  MAX('BusinessDays'[BusinessDays]) extract the max value in the whole table instead of current value in each row according to context.

My solution is firstly create a variable table with ADDCOLUMNS function adding the previous measure as a column in the measure then sum the column, basicly it's like below:

Measure =
VAR _T =
    ADDCOLUMNS (
        'AIS_New',
        "calculate field",
            DIVIDE (
                AVERAGE ( 'AIS_New'[AISHours] ),
                SUM ( 'AISLocation_new'[HoursPerDay] ) * EARLIER ( 'AIS_New'[ChairNumber] )
                    * RELATED ( 'BusinessDays'[BusinessDays] )
            )
    )
RETURN
    IF (
        ISINSCOPE ( 'AIS_New'[ChairNumber] ),
        DIVIDE (
            AVERAGE ( 'AIS_New'[AISHours] ),
            SUM ( 'AISLocation_new'[HoursPerDay] ) * MAX ( 'AIS_New'[ChairNumber] )
                * MAX ( 'BusinessDays'[BusinessDays] )
        ),
        SUMX ( _T, [calculate field] )
    )

 As I'm not clear about your data model, maybe you should tweak it according to your sample.

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rocku2008_1985]

Could anybody help me out regarding this?