Removing zeros from a measure

[RichOB]

Hi, I'm looking to remove the zeros from my measure so the graph only takes into account the quarters with data.

Measure I used to combine the regions:

RegDiv = [North] + [SouthW] + [SouthE]

Measure I used to divide the above by 3:

RegAvg = [RegDiv] / 3

As Q4 does not have North data, it's still dividing by 3 which I do not want. Is there a way to not include blank or zero entries in the calculation?

For more context, before I got to the line graph above, I had to separate the regions individually from the column as the line graph was not allowing me to add the Region AND the Total Score(averaged) line...but if I separated them individually it did. Also, if there's any easier way I'm keen to hear it. The data is structured like this FYI:

Region	Score
South East	4
South West	5
North	6

Thanks for your help

[Anonymous · Accepted solution]

Hi @RichOB ,

Thanks @bhanu_gautam  for the quick reply. Please allow me to offer an alternative idea:

(1) This is my test data.

We can create a column on Table.

Avg = AVERAGEX(FILTER('Table',[Quarter]=EARLIER('Table'[Quarter])),[Score])

(2) We can create a table [Table2].

Table 2 = ADDCOLUMNS(VALUES('Table'[Quarter]),"Region","RegAvg")

Then create a column on Table2.

Score = CALCULATE(MAX('Table'[Avg]),FILTER('Table',[Quarter] =EARLIER('Table 2'[Quarter])))

(3) Then we can create a table [Table3].

Table 3 = 
var _a=SUMMARIZE('Table',[Quarter],[Region],[Score])
RETURN UNION(_a,'Table 2')

Best Regards,

Neeko Tang

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

[bhanu_gautam]

@RichOB , You can create a measure to count non zero 

NonZeroCount =

   COUNTROWS(

       FILTER(

           VALUES('YourTable'[Region]),

           [North] <> 0 || [SouthW] <> 0 || [SouthE] <> 0

       )

   )

Then create one more measure 

   RegAvg = DIVIDE([RegDiv], [NonZeroCount])