Calculate the Average for only the Month that have the score

[py1029]

Hi,

I'm having a problem when calculating a score for a KPI in Power BI. Below is the table that showing in my dashboard.

The financial year is starting at July. It has the same target (22%) for every month. The Achievement is calculated by using Actual/Target. When I want to calculate the score in a YTD basis, I would like to average the monthly scoring that with scoring only. (0% means there is no scoring for that month)

For example, I want the calculation at Nov be like: (27%+18%)/2 = 22.5%  

Currently, I'm using AVERAGE dax function in my measure which will take into account the 0% as well and become (0%+27%+18%+0%+0%)/5 = 9%  and this is not the result that I want.

Is there anyway I can calculate the average of score (YTD) be like this ? (27%+18%)/2 = 22.5%  

[V-lianl-msft]

Hi @py1029 ,

Try to create a measure like below:

average of score = CALCULATE( AVERAGE('Table'[Actual]), FILTER('Table', 'Table'[Actual] <> 0 ))

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[py1029]

Hi, 

I have tried on this measure but it caused a problem that for the Month which have 0% won't be showing out and the column "Actual" in the table visual should be the YTD of it (22.5%) as attached below:

Thanks!

[lbendlin]

I am amazed how often this question comes up.   Please explain the business reasoning behind your expectation.

[py1029]

Hi.

This happened when I want to calculate the Gross Margin for an individual. As there might not have any projects for every month and this concept can be explained as acumulative basis. For example:

Thanks!

[lbendlin]

I still don't get the business case but attached is an implementation.

[py1029]

Hi,

Will try on your solution soon. Thanks for the reply!