Calculate Measures - Force Zeros from blanks

[sfernamer]

Hi everyone! 

I'm working with basketball data and there is a speficic situation where I can have a game (field Full_Game) where there is no specific Situation field value. To sum up about the measures used:

- TotalPts = SUM(Hoja1[Court Pts])+SUM(Hoja1[FT Pts])

- Full_Games = CALCULATE(DISTINCTCOUNT(Hoja1[Full_Game]),ALLSELECTED(Hoja1[Competition]))

- Pts_MYT = CALCULATE('Game_Measures'[TotalPts],Hoja1[Offensive]="MYTEAM")

- Pts_MYT/G = DIVIDE('Game_Measures'[Pts_MYT],'Game_Measures'[Full_Games],0)

Checking the image below, you can see that there are Blanks for Pts_MYT/G in the matrix that relates games, game situations and Pts_MYT/G. The reason why there are blanks is due to the fact that there are no rows for the specific situation (Handoff) in certain games. That's why TotalPoints and Full_Games are BLANK, not 0, and also Pts_MYT/G. The measures are:

- TotalPts = SUM(Hoja1[Court Pts])+SUM(Hoja1[FT Pts]) - The calculated value is blank, the one needed is 0. 

- Full_Games = CALCULATE(DISTINCTCOUNT(Hoja1[Full_Game]),ALLSELECTED(Hoja1[Competition])) - The calculated value is blank, because there are no rows. The value needed here is 1 (because, in terms of business, there can be a game with no rows of values from some columns).

- Pts_MYT = CALCULATE('Game_Measures'[TotalPts],Hoja1[Offensive]="MYTEAM") - Dependency with Total_points

- Pts_MYT/G = DIVIDE('Game_Measures'[Pts_MYT],'Game_Measures'[Full_Games],0) - The calculated value is blank, the one needed is 0,00 for the games where we find a blank.

Add here the pbix file: https://drive.google.com/drive/folders/1BxVWCtoHkcQYnDfOpqmYFpFgXpoEIPOt?usp=drive_link 

Thank you for your time.

[rbriga · Accepted solution]

There are (at least) 2 ways to solve this.

1. At the source: In the query editor, replace null values with 0 for fields like [FT Pts], [Court Pts] and others.

This means they'll have a value, 0, rather than nothing. It will make SUM and DIVIDE work in the way you expect them to.

2. COALESCE(): COALESCE() replaces blanks with zeroes- but beware. It will show 0 to combinations of dates, teams and players that shouldn't be there. That's why you may need to create a condition to the calculation, such as:

Total Points=
VAR _Played = [Total Games]
RETURN
IF(ISBLANK(_Played), BLANK(),
SUM(Hoja1[Court Pts])+SUM(Hoja1[FT Pts])
)

My suggestion: #1, at the source. Push the logic and calculations into the query.

Make sure those are numerical valuesreplace null with 0