Forum Discussion
Combine Calculated numerical column with text formatted unit column
MPRGray -
I hope someone comes up with a better solution. Creating a new table using GROUPBY ( CALCULATETABLE ( ) ) what I came up with:
Table = GROUPBY ( CALCULATETABLE ( TableName, TableName[CIP Concrete Install Status] = "3) Poured/Finished" ), TableName[Activity ID], TableName[Unit], "Total", SUMX ( CURRENTGROUP (), TableName[CIP Poured/Finished] ) )
https://www.dropbox.com/s/fkadl1b60xrkh4j/Sample%20CIP%20Data%20set.xlsx?dl=0
Okay this is a sample data set that i exported to excel.
Columns F-K in the attached workbook were made in Power Query.
the code for them is as follows:
= Table.AddColumn(#"Added Custom3", "CIP Not Installed", each if [CIP Concrete Install Status] = "Not Installed" then [Takeoff Quantity] else null)
= Table.AddColumn(#"Added Conditional Column", "CIP Formed", each if [CIP Concrete Install Status] = "1) Formed" then [Takeoff Quantity] else null)
= Table.AddColumn(#"Added Conditional Column4", "CIP Rebar Set", each if [CIP Concrete Install Status] = "2) Rebar Set" then [Takeoff Quantity] else null)
= Table.AddColumn(#"Added Conditional Column1", "CIP Poured/Finished", each if [CIP Concrete Install Status] = "3) Poured/Finished" then [Takeoff Quantity] else null)
= Table.AddColumn(#"Added Conditional Column2", "CIP Cured", each if [CIP Concrete Install Status] = "4) Cured" then [Takeoff Quantity] else null)
= Table.AddColumn(#"Added Conditional Column3", "CIP Epoxy-ed/Complete", each if [CIP Concrete Install Status] = "5) Epoxy-ed/Complete" then [Takeoff Quantity] else null)
I need a table showing Activity ID and the quantity of each unit for each installation status with both the correct amount and the Unit contained within the same cell.
Please let me know if this is not enough information.
Thanks,
MPRGray -
I hope someone comes up with a better solution. Creating a new table using GROUPBY ( CALCULATETABLE ( ) ) what I came up with:
Table =
GROUPBY (
CALCULATETABLE (
TableName,
TableName[CIP Concrete Install Status] = "3) Poured/Finished"
),
TableName[Activity ID],
TableName[Unit],
"Total", SUMX ( CURRENTGROUP (), TableName[CIP Poured/Finished] )
)