AverageX is not working as expected when the cell is blank

[sagarsahoo_123]

Hi Team,

I have a small requirement. I want  to use AverageX function considering the blank cell when calculating.

For better explanation i am attaching a simple sample set below.

Month	Total=(A+B)	A	B
Oct	2		2
Nov	2		2
Dec	3	1	2
Expected Total	2.333	0.33	2

In this my total is combinaton of Column A and B. If you look at the column A is having 2 blank cell and 3rd cell for Dec month is 1.

When applying AverageX function selecting 3month in slicer like Oct,Nov, Dec it is giving me 1, but my expectation is as it has to consider the total value 1 against 3month. So i am expecting the result of Total for column A would be 0.33 .

How Can achieve this, please help!

Total = AVERAGEX(VALUES([Month]),sum[A])

Regards,

Sagar

[amitchandak]

@sagarsahoo_123 , Use calculate OR use measure in expression of sumx

Total = AVERAGEX(VALUES([Month]),calculate( sum[A]) ) )

[sagarsahoo_123]

Hi @amitchandak ,

Thanks for your quick response. But calculate function is  already used in the measure but it is not giving the desired result for multiple month selection. The problem comes when the value for any month is blank.

please find the actual code used in the dashboard.

The above image is for the period of Oct to Dec. And in which both Nippon and Digi ACT HC count is <= Total HC. To bring average value for mulltiple month i have used AverageX function.

Code:-

Nippon HC = AVERAGEX(VALUES(FY_Calendar[Month]),[Nippon HC_D1])

Nippon HC_D1 = calculate(SUM(VW_NIPPON_PRODUCTIVITY[FTE]), NOT(ISBLANK(VW_NIPPON_PRODUCTIVITY[NIPPON_STATUS])))

--------------

Please find the image only selecting single month.

Selecting only Oct month.

Selecting only Nov month

Selecting Dec month

So single month selection, the value is coming correctly but problem comes when multiple month are selected and its average for Nippon HC is coming as 1, but it has to be 0.33 which is for 3 month. Though in Oct and Nov there is no value for Nippon HC but when averaging for all 3 months, it should consider the Oct and Nov as well.

Thanks for your support.

Regards,

Sagar

[sagarsahoo_123 · Accepted solution]

Hi @amitchandak ,

Got a way to bring the expected result i am looking for. I converted the blank cell value as Zero, then AverageX considered for all selected months and provided the desired result.

Added the ISBLANK function to address the blank cell value.

--------------------------

Nippon HC = AVERAGEX(VALUES(FY_Calendar[Month]),

    IF(

        ISBLANK([Nippon HC_D1]),

        0,

        [Nippon HC_D1]

    )

  )

----------------------

Now for OND period the table is coming as per expectation.

Thanks a lot for your valuable inputs.

Regards,

Sagar

[Deevo_]

@sagarsahoo_123 Thank you very much. Your formula worked for me!