Calculate not working

It's quite challenging to get an answer from this information.  It relies on knowing advanced SQL and then creating a mental model in one's head then trying to convert that to a powerbi model.

The above 2 queries aren't doing the same thing anyway.  The SQL returns a temp table with 2 columns after doing a self-join on another temp table.  We don't know if Awaiting is a column or measure.

It worries me that the powerbi table is called Query1, rather than Sales or something like that.

I suggest that you post some data and desired output with an explanation of your model and whether you want a calculated column or measure.  Someone will help you.