Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

  • Anonymous's avatar
    Anonymous
    6 years ago

    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

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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

  • saraMissBI's avatar
    saraMissBI
    Icon for Resolver I rankResolver I
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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