Need Average of each line item in Total

DAX DAX Calculations
1

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)

Snapshot

2

@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
3

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] )”

4

@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 )
    )
)

image
image861×480 28.8 KB

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
5

Thank you Antriksh.

It’s perfectly working.

Stay safe
Niraj

1 Like