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
Bookmarks