cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Removing zeros from a measure

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

1 ACCEPTED SOLUTION
Community Support

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.

2 REPLIES 2
Community Support

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.

Super User

@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])

 Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors