Combine Calculated numerical column with text formatted unit column

[MPRGray]

I am trying to use this to track progress for concrete installation. Each individual piece of concrete has it's own row in the table. I have found a workaround in case I can't find a solution here, but please bear with me as this will be a lengthy explanation. 

I have a Column titled "CIP Concrete Install Status", with the 6 different possible values being: "Not Installed, 1) Formed, 2) Rebar Set, 3) Poured/Finished, 4) Cured, 5) Epoxy-ed/Complete.

There is a separate Column titled "Takeoff Quantity", which provides accurate measurement of each individual piece, based on what unit of measurement you are tracking it by.

There is a column for "Unit", which contains the possible values: "LF,SF,CY,EA". This column denotes what unit of measurement each individual piece of concrete's Takeoff Quantity is associated with. 

I have been able to create individual calculated columns to determine the amount of each unit has been assigned to each status, but it is incredibly cumbersome and makes visualizing the data way more difficult. 

First I created a calculated column for Linear Footage: 

LF = IF('Grid Data'[Unit]="LF",'Grid Data'[Takeoff Quantity],0)

Then I created calculated columns to determine the amount of LF of concrete is assigned each status. For Example:

CIP LF Poured/Finished = IF('Grid Data'[CIP Concrete Install Status]="3) Poured/Finished",'Grid Data'[LF],0)

Again, this works fine and displays the information accurately, but the only way the unit of measure can be determined is by the column title. 

I want to be able to have 1 column that displays All concrete that has been Poured/Finished, rather than creating 4 individual columns titled "CIP LF Poured/Finished", "CIP SqFt Poured/Finished", "CIP CY Poured/Finished", "CIP EA Poured/Finished

" and then repeating that for the other 5 statuses, thus having 20 calculated columns. There is no way that will be readable on a table.

I tried to Concantacate, but when i do that it won't sum the values anymore. 

here is what i wrote, please tell me where i'm going wrong:

CIP Unit Poured/Finished = FORMAT(IF('Grid Data'[CIP Concrete Install Status]="3) Poured/Finished",'Grid Data'[Takeoff Quantity],0),"###,##0.00"&" "&'Grid Data'[Unit])

It correctly combines the Unit and the number in a single column, but it won't sum the values anymore. There should be 180 LF of concrete Poured/Finished, but It only displays 20 LF (There are 9 individual pieces that are 20 LF in length).

only 20 LF is displaying on the right column, when it should be 180 LF

[ChrisMendoza]

@MPRGray -

Did you try https://www.sqlbi.com/articles/using-concatenatex-in-measures/? Providing sample data is a good idea.

[MPRGray]

Thanks, I haven't tried that yet. I'm still pretty new to Power BI and have a bit of trouble with measures. 

How would i reference a column like "CIP Install Status" in a measure?

Really not sure where to start with figuring this out. Sorry!

[ChrisMendoza]

@MPRGray -

While you've taken the time to explain your data, I am still unsure of how your dataset looks. Please provide a sample dataset that we can try to assist with. You will have a better response from the community. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[MPRGray]

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,

[ChrisMendoza · Accepted solution]

@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://dax.guide/groupby/

https://dax.guide/calculatetable/