Calculate - Forcing Blanks and Calculate Averages and Top5

[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.

I tried to calculate the measures to get the expected values (0 for Total Points and 1 for Full_Games). It worked (as you can see in the 1st image but looks like it's not working to calculate the Last Game amd Last 5 because I don't know how to force the Date to be counted and show the value).

Could you help me to get it? The expected results in the matrix shown in the image should be:

Pts_Last Game = 0.00 (Date = 19/01/23)

Pts_Last5 Game = 1.2 (6 [Blank to 0+0+3+Blank to 0+3] /5 - Dates: 22/12 - 30/12 - 05/01 - 10/01 - 12/01)

Add here the pbix file where you can find all info and measures: https://drive.google.com/drive/folders/1BxVWCtoHkcQYnDfOpqmYFpFgXpoEIPOt?usp=drive_link 

Thank you for your time.

[Anonymous]

Hi @sfernamer,

In fact, these blank part means your table not include records match with that row/column category values and the calculations will skip on these parts.
You may need ot create a table with crossjoin function to stored all the scenarios and compare with the raw table value and use this as condition to calculate.
Regards,

Xiaoxin Sheng

[sfernamer]

Hi @Anonymous ,

Firstly, I'd like to thank you your time and apologize for the late reply. I don't totally understand your point.

I was checking your reply. It's true that the data recorded does not contain the records that match (it's correct in terms of business but in these cases it's preferable the 0 instead of blank). For what I checked, the crossjoin function would allow me to do a full join but not adding new rows, what maybe could work. Or maybe I got wrong?

Thank you for your time and patience.

[Anonymous]

Hi @sfernamer,

In your descpriton, you said these blank parts also need to be calculated.

AFAIK, Dax calculations will skip blank parts and you also not able to replace to zero on these because they are not existed. (filtered by 'auto existed' feature with high priority)

For this scenairo, you need to use Crossjoin to get the correct item amount, then you can use this as variable to calculate with aggreated values.
Regards,

Xiaoxin Sheng