Data Model Pivot Table - Measure Issue

Hi there, not sure if I can easily explain what I am trying to do... but will give it a shot.

I have two tables that I have added to the data model (using Excel 365 ProPlus) (Table 1 - with Sales & Table 2 with Goals) with assigned groups (A & B).

Description for attached image...

PIVOT1 (Pivot Table) shows the rows by location, adding groups, AVG_SALES from Table 1 and AVG_GOAL from Table 2.
AVG_SALES = Average([Sales])
AVG_GOAL = Average([Goal])

As you can see that PIVOT1 shows an AVG_GOAL ($5) value but has no AVG_SALES value adjacent. My objective is to remove the AVG_GOAL ($5) value if no matching AVG_SALES value exist.

In PIVOT2 (Pivot Table), I created new measures (TTL_SALES & TTL_GOAL) still by Location. Which does exactly what I need it too.

TTL_SALES = if(isblank([AVG_GOAL]),blank(),[AVG_SALES])
TTL_GOAL = if(isblank([AVG_SALES]),blank(),[AVG_GOAL])

As you can see... the TTL_GOAL ($5), no longer appears.


My issue is that I also need to create a pivot table (PIVOT3) that does the same thing by group.
As you can see in PIVOT3, the TTL_GOAL value is averaging with the $5 included...

How do I work the measures to remove the $5, like it did in PIVOT2?

Pivot Table (PIVOT3) TTL_GOAL should read $70!

Is there a better way to make this work? Been pulling my hair out for quite a few hours today...

Sample.PNG


Thanks
Craig