Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Subtracting Grouped Rows in Different Tables

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
7970386A
7970386B
7970386C
7970387A
7970387B
......

 

Table 2 (Summary)

Work Order #Total Construction Locations
7970386372
7970387106
7970388271
......

 

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

6 REPLIES 6
saraMissBI
Resolver I
Resolver I

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
Not applicable

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

Hi @Anonymous ,

 

Sorry here is the link it should work for you now

 

Thank you

Anonymous
Not applicable

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
Super User
Super User

@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
Not applicable

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors