Forum Discussion

ankit_cd's avatar
ankit_cd
Regular Visitor
4 years ago
Solved

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

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 li...
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

lbendlin's avatar
lbendlin
Super User
4 years ago

"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's avatar
ankit_cd
Regular Visitor
4 years ago

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!