How to plot values that aggregates average only for non-zero values?

[ankit_cd]

A particular category has 5 sub-categories. After a particular year, only 3 sub-categories are active. I am trying to plot different metrics such as quality, efficiency, charges, etc. in different line plots as an average value over the last decade. I want to include only non-zero values in the average that is being plotted. Currently, the automatically aggregated average in Power BI includes even the zero values.

The below chart is aggregated average for 5 sub-categories. The values (around 2.3k) shown for 2018-2020 are wrong.

 Avg. of 5 sub-categories.

The below chart is aggregated average for 3 sub-categories. The values (around 4k) shown for 2018-2020 are right.

Avg. of 3 sub-categories.

How can I correct this and aggregate average only for non-zero values?

[Anonymous · Accepted solution]

HI @ankit_cd,

Did you mean to prevent the aggregation calculation on zero and negative values?
If that is the case you can write a measure formula with the filter on the table to exclude the negative values.

formaul =
CALCULATE (
    SUM ( Table[Amount] ),
    FILTER ( ALLSELECTED ( Table ), [Amount] >= 0 ),
    VALUES ( Table[Category] )
)

Regards,

Xiaoxin Sheng

[ankit_cd]

Thank you very much kind person. This works!

[lbendlin]

"After a particular year, only 3 sub-categories are active"

How is Power BI supposed to know that?  Do you have a reference table that shows which subcategories were active when?

If the two subcategories that are now no  longer active do not contribute any data (either BLANK() or nothing, but not 0) then the Average calculation should automatically consider that.

[ankit_cd]

Thanks for the reply kind person. I haven't tried this method because for some of the columns I want to show the values as 0 and not blank or nothing. Also yes, I have a column that indicates which sub-categories are active in a particular year!