Subtracting Grouped Rows in Different Tables

[Anonymous]

Hello,

I'm managing construction locations and I have 2 tables. Here are a few sample columns.

Table 1 (Locations)

Work Order #	Work Location Tag
7970386	A
7970386	B
7970386	C
7970387	A
7970387	B
...	...

Table 2 (Summary)

Work Order #	Total Construction Locations
7970386	372
7970387	106
7970388	271
...	...

My objective is to find out how many locations haven't started yet, which would follow the logic "Total Construction Locations - # of Work Locations per WO #". So basically if a location has a Work Location Tag, I assume assume it has started. 

I would like to add this column next to the Total Construction Locations so I can eventually compare Total vs Not Started vs In Progress. 

Thanks in advance for your help. 

Robyn

[saraMissBI]

Hi @Anonymous 

Please find here a pbix file with the solution to your problem.

If it solves the issue, please give Kudos and mark as solution.

Thanks!

[Anonymous]

Hello @saraMissBI ,

Unfortunately the file is stored at alakhawayn365-my.sharepoint.com and I can't access it. Can you post it differently or I can PM you my email address. 

Robyn

[saraMissBI]

Hi @Anonymous ,

Sorry here is the link it should work for you now

Thank you

[Anonymous · Accepted solution]

Hey all,

Thanks for the help. I ended up figuring out the solution. Here's the equation below...

In Progress/Complete Locations = countx(filter('Table1', 'Table1'[Work Order #] = 'Table 2'[Work Order #]), 'Table1'[# of Locations Complete/In Progress])

Not Started Locations = Total Construction Locations - In Progress/Complete Locations

[amitchandak]

@Anonymous , Not very clear

New column in Table 2

if(isblank(Countx(filter(Table1,table1[Work Order #] =table2[Work Order #]),[Work Location Tag])),"Not Started","In Progress")

[Anonymous]

Hello @amitchandak ,

Apologies. I am looking for a number value.

For example if I have a WO with 21 Total Construction Locations from Table 2 and I have 5 Locations listed in Table 1 under that WO, I would like a column in Table 2 to say 16 Locations Not Started for that WO.

Robyn