Hi All,

I have a requirement to calculate average of each line item in Grand Total field. Though i have tried this with two different formulas, it’s still showing the same result.

I have attached the pbix working file for your reference. My ask is to calculate average of blue highlighted field and replace the result in red highlighted as per attached jpeg image.

Hope some can put some light on this.

Average Calculation.pbix (28.2 KB)

@nbaraili

```
Utilization_Average % =
IF (
ISINSCOPE ( data1[Region] ),
VAR Base_table =
ADDCOLUMNS (
VALUES ( data1[Region] ),
"Util", CALCULATE ( DIVIDE ( SUM ( data2[Hours] ), SUM ( data1[Adjusted_Count] ) ) )
)
VAR Ave_Util =
AVERAGEX ( Base_table, [Util] )
RETURN
Ave_Util
)
```

1 Like

Hi Antriksh,

Thank you very much for the quick response.

After applying the above formula, now Total seems blank.

Actually, i need the average of total line items in Total instead of “DIVIDE ( SUM ( data2[Hours] ), SUM ( data1[Adjusted_Count] )”

@nbaraili Oh I pasted the wrong code you can use this:

```
Utilization_Average % =
AVERAGEX (
VALUES ( data1[Region] ),
CALCULATE (
DIVIDE ( SUM ( data2[Hours] ), SUM ( data1[Adjusted_Count] ), 0 )
)
)
```

If you face performace issues then you can use this:

```
Utilization_Average % =
AVERAGEX (
ADDCOLUMNS (
VALUES ( data1[Region] ),
"@Utils",
CALCULATE (
DIVIDE ( SUM ( data2[Hours] ), SUM ( data1[Adjusted_Count] ) )
)
),
[@Utils]
)
```

1 Like