New column for SUM based on a value in another column

[datavis]

Need to combine PO Hours for PO Lines 1 and 2 into a new column. See visual.

Thanks in advance.

[v-yulgu-msft]

Hi @datavis,

One simple solution is adding [Contractor] and [PO Hours] into a table visual.

Alternatively, you can create a calculated table.

result table = SUMMARIZE(Table1,Table1[Contractor],"Total PO Hours",SUM(Table1[PO Hours])) 

Best regards,

Yuliana Gu

[datavis]

Thank you,

In my first table, 'FY19', I created a column for every month of the year by subtracting the number of hours available to work in each month. After the month is over, I will get new data and subtract the number of hours worked. I used this in a clustered column chart.

This is how I created September which is the first month in the fiscal year.

01Sep 2018 Remaining Hours = 'FY19'[POHours]-152

Subsequent months use the previous month's hours left to subtract hours avaliable or worked as shown for October below.

02Oct 2018 Remaining Hours = 'FY19'[01Sep 2018 Remaining Hours]- 184

Some of the contractors have two PO Lines so I created a new table to sum the hours as follows:

'POLineTotal'[TotalPOHours]

How can I point to the new table to use 'POLineTotal'[TotalPOHours] instead of 'FY19'[POHours] for September?

[Greg_Deckler]

If you want this as a column, then:

Total PO Hours = 
SUMX(FILTER(ALL('Table'),[Contractor]=EARLIER([Contractor])),[PO Hours])

[datavis]

Hi Greg,

You did answer my question but I need to sum the PO Hours in PO Line 1 and PO line 2 together for the contractors who have two line items (each on a separate row/record). I tried filtering the PO Lines but it did not sum them. The new table suggested by someone else works great but now my data is in a another table and I need to create a relationship to use it. I am working on that  now. Any suggestions on creating a relationship appreciated.

[datavis · Accepted solution]

I figured it out - just fyi. When I created the new table as instructed to SUM both PO Lines and eliminate the PO Line 2 rows to prevent it from showing two visuals for one person, it did not have the director column. I added the director column from the original data file using by adding it the following formula that was used to create the new POLineTotal table and it worked. Thank you everyone -Yuliana, Frank, and Greg - for your help. Takes a village.

POLineTotal = SUMMARIZE(FY19RemainingHoursReport,FY19RemainingHoursReport[Director],FY19RemainingHoursReport[Contractor, PO, End Date],"Total PO Hours",SUM(FY19RemainingHoursReport[ PO Hours]))