topic Re: Average in DAX Commands and Tips

Average

Nathaniel_C — Sun, 14 Aug 2022 23:24:15 GMT

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

Re: Average

Jihwan_Kim — Mon, 15 Aug 2022 03:16:09 GMT

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

Re: Average

CNENFRNL — Mon, 15 Aug 2022 03:31:57 GMT

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 )

Re: Average

tamerj1 — Mon, 15 Aug 2022 05:13:36 GMT

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

Re: Average

Nathaniel_C — Sat, 20 Aug 2022 23:11:25 GMT

Hi Jihwan_Kim tamerj1 CNENFRNL ,
Thank you all,

Three different solutions, that all work!

Nathaniel