Average

[Nathaniel_C]

Hi All,

This works with a single month selected, but not with multiple months.  Trying to show the average of what shows in the visual, and then color format the column background.

This is my pbix 

  

Thank you!

Nathaniel

[Jihwan_Kim · Accepted solution]

Hi,

Could you please try the below whether it provides the result that you want?

Average =
AVERAGEX (
    FILTER (
        ADDCOLUMNS (
            VALUES ( 'DataTable'[Name] ),
            "@ERecsperhour", CALCULATE ( SUM ( 'DataTable'[E-Recs per Hour] ) )
        ),
        [@ERecsperhour] <> 0
    ),
    [@ERecsperhour]
)

[CNENFRNL · Accepted solution]

Average = AVERAGE('DataTable'[E-Recs per Hour])
= AVERAGEX('DataTable', 'DataTable'[E-Recs per Hour])

the calculation of average is based on the rows of 'DataTable' in the current filter context; it has nothing to do with the calculation in the table viz.

AVG = 
VAR __n = VALUES( 'DataTable'[Name] )
RETURN
    SUMX( __n, CALCULATE( SUM( 'DataTable'[E-Recs per Hour] ) ) )
        / COUNTROWS( __n )

[Nathaniel_C]

Hi @Jihwan_Kim @tamerj1 @CNENFRNL ,
Thank you all,

Three different solutions, that all work!

Nathaniel

[tamerj1 · Accepted solution]

Hi @Nathaniel_C 
Here is the sample file with the solutoion https://www.dropbox.com/t/M7zX39R3UdSCO9SR

You want to calculate the average of the sum. The row level average won't help. Can follow this 

Average New = 
AVERAGEX ( 
    VALUES ( 'DataTable'[Name] ),
    CALCULATE ( SUM ( 'DataTable'[E-Recs per Hour] ) )
)

[CNENFRNL · Accepted solution]

Average = AVERAGE('DataTable'[E-Recs per Hour])
= AVERAGEX('DataTable', 'DataTable'[E-Recs per Hour])

the calculation of average is based on the rows of 'DataTable' in the current filter context; it has nothing to do with the calculation in the table viz.

AVG = 
VAR __n = VALUES( 'DataTable'[Name] )
RETURN
    SUMX( __n, CALCULATE( SUM( 'DataTable'[E-Recs per Hour] ) ) )
        / COUNTROWS( __n )

[Jihwan_Kim · Accepted solution]

Hi,

Could you please try the below whether it provides the result that you want?

Average =
AVERAGEX (
    FILTER (
        ADDCOLUMNS (
            VALUES ( 'DataTable'[Name] ),
            "@ERecsperhour", CALCULATE ( SUM ( 'DataTable'[E-Recs per Hour] ) )
        ),
        [@ERecsperhour] <> 0
    ),
    [@ERecsperhour]
)