The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
Need to combine PO Hours for PO Lines 1 and 2 into a new column. See visual.
Thanks in advance.
Solved! Go to 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]))
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
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?
If you want this as a column, then:
Total PO Hours = SUMX(FILTER(ALL('Table'),[Contractor]=EARLIER([Contractor])),[PO Hours])
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.
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]))
Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.
User | Count |
---|---|
158 | |
106 | |
96 | |
83 | |
75 |
User | Count |
---|---|
153 | |
137 | |
131 | |
81 | |
61 |