topic Re: Calculating Average while ignoring zero/null values in DAX Commands and Tips

Calculating Average while ignoring zero/null values

Green_Cloud — Fri, 04 Nov 2022 13:50:17 GMT

Hi All,

I need to find out average based on the following dataset. But I just need to show the available month's value in the average when one of the month's value is null or zero. The formual I used for average is:

Two Month's Average = ((CALCULATE(SUM([Working Hour]), FILTER('Sample','Sample'[Month]="February"))) + (CALCULATE(SUM([Working Hour]), FILTER('Sample','Sample'[Month]="March"))))/2

Dataset:

I have the current output as follows. But it is inaccurate when the filtered value is either 'Alex' or 'John' as they have zero working hours in one of the months. I need a dax which can ignore null/zero values. The desired average value for 'Alex' should be 4 and for 'John', it should be 3.

Below is the Power BI file link-

https://drive.google.com/file/d/1vRA6h82VIT7zvvtFczW3eJnr-gxXC-z2/view?usp=share_link

Thanks

Re: Calculating Average while ignoring zero/null values

Jihwan_Kim — Fri, 04 Nov 2022 05:03:36 GMT

Hi,

I am not sure if I understood your question correctly, but please try something like below.

Please check the attached pbix file.

Avg measure: = CALCULATE ( AVERAGE ( 'Sample'[Working Hour] ), FILTER ( 'Sample', 'Sample'[Working Hour] <> 0 ) )

Re: Calculating Average while ignoring zero/null values

Green_Cloud — Fri, 04 Nov 2022 13:36:46 GMT

Jihwan_Kim , Thanks so much for your solution! It's close but I needed to make a slight change in the dataset. I needed to filter out the data only for February and March to calculate average. Attached is the link with updated file-

https://drive.google.com/file/d/1vRA6h82VIT7zvvtFczW3eJnr-gxXC-z2/view?usp=share_link

Re: Calculating Average while ignoring zero/null values

Jihwan_Kim — Fri, 04 Nov 2022 14:40:45 GMT

Hi,

Thank you for your feedback.

Could you please check the below measure and the attached file, whether it suits your requirement?

Average (February & March) = ( ( CALCULATE ( SUM ( [Working Hour] ), FILTER ( 'Sample', 'Sample'[Month] = "February" ) ) ) + ( CALCULATE ( SUM ( [Working Hour] ), FILTER ( 'Sample', 'Sample'[Month] = "March" ) ) ) ) / COUNTROWS ( FILTER ( 'Sample', 'Sample'[Month] IN { "February", "March" } && 'Sample'[Working Hour] <> 0 ) )

Re: Calculating Average while ignoring zero/null values

Green_Cloud — Fri, 04 Nov 2022 16:42:54 GMT

edited

Re: Calculating Average while ignoring zero/null values

Green_Cloud — Fri, 04 Nov 2022 17:30:40 GMT

Thanks so much Jihwan_Kim . It worked. I also need to find an average of conversion rate as follows. But the average is not accurate when it comes to the average conversion of of 'David' or 'Maya'. For David, it should be- 108.335 and for Maya-30.555

Below is the updated PBI file-
https://drive.google.com/file/d/1MPwUn89yxiUYwSWpJKispl7LesBJQd9Y/view?usp=share_link

The Dataset-

Re: Calculating Average while ignoring zero/null values

Jihwan_Kim — Sat, 05 Nov 2022 04:50:35 GMT

Hi,

Please check the attached file whether it suits your requirement.

Thanks.

Re: Calculating Average while ignoring zero/null values

Green_Cloud — Sun, 06 Nov 2022 05:21:47 GMT

It should ignore the 0% conversion rate for John and Alex. It is considering that.