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

[Trostis]

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.

[tamerj1]

Hi @Trostis 

please try

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

[Trostis]

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

[tamerj1 · Accepted solution]

Hi @Trostis 

please use

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

[Trostis]

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.

[tamerj1]

@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