topic Calculating the daily average of the displayed range when some dates have no data. in DAX Commands and Tips

Calculating the daily average of the displayed range when some dates have no data.

Trostis — Mon, 21 Nov 2022 18:32:58 GMT

Hi folks,

I have a simple matrix visual showing the daily total of some production metric grouped per categories; I use a slicer to select any date range; some categories have data all days, while others don't. I want to get the average for the displayed range in the visual, no matter if the column has values for all rows; so if I'm displaying 7 days, all totals should be divided by 7. Cannot get that to work and always get the average of the existing rows in each category; so if all rows have data the result is correct (green circles), but if some date has no production, the result is wonrg (red circle). Any suggestions?

I'm using

AVG =

AVERAGEX(

KEEPFILTERS(VALUES('Table'[Día Operativo])),

CALCULATE(SUM('Table'[Product]))

)

Thanks and regards.

Re: Calculating the daily average of the displayed range when some dates have no data.

tamerj1 — Mon, 21 Nov 2022 19:58:58 GMT

Hi Trostis

please try

AVG =
AVERAGEX (
KEEPFILTERS ( VALUES ( 'Table'[Día Operativo] ) ),
COALESCE ( CALCULATE ( SUM ( 'Table'[Product] ) ), 0 )
)

Re: Calculating the daily average of the displayed range when some dates have no data.

Trostis — Tue, 22 Nov 2022 14:44:16 GMT

Thanks @tamerj1, but no luck...same result. No changes at all that I can see when applying your recommendation. Regards.

Re: Calculating the daily average of the displayed range when some dates have no data.

tamerj1 — Tue, 22 Nov 2022 15:00:59 GMT

Hi Trostis

please use

AVG =
AVERAGEX (
KEEPFILTERS (
CALCULATETABLE ( VALUES ( 'Table'[Día Operativo] ), ALL ( 'Table'[Category] ) )
),
COALESCE ( CALCULATE ( SUM ( 'Table'[Product] ) ), 0 )
)

Re: Calculating the daily average of the displayed range when some dates have no data.

Trostis — Tue, 22 Nov 2022 15:19:38 GMT

Excellent!!! That did work as I needed it!!! Now I'm exceeding myself with this, I know, but what the heck is that expression doing? At least the new CALCULATETABLE part...again, really appreciate your help. Regards.

Re: Calculating the daily average of the displayed range when some dates have no data.

tamerj1 — Tue, 22 Nov 2022 15:45:34 GMT

Trostis

I didn't pay much attention at the first time. I dodn't notice that you are slicing by Category at the columns of your matrix. Which means the values are not just blank, rather they do not exist.
in your example, the iteration table VALUES ( 'Table'[Día Operativo] ) contains only two rows. In order to iterate over all of the 6 rows we need to calculate the same table but for all the categories becuase category A contains only two rows but all the categories contain all of the 6 rows. Therefore, AVERAGEX will sum the existing two values + 0 for the nonexisting onse then divide over 6