cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## Combine Calculated numerical column with text formatted unit column

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

1 ACCEPTED SOLUTION
Resident Rockstar

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/

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!

5 REPLIES 5
Resident Rockstar

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

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!

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?

Resident Rockstar

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

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!

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,

Resident Rockstar

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/

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors