Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
MPRGray
Advocate I
Advocate I

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 LFonly 20 LF is displaying on the right column, when it should be 180 LF

1 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/






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



View solution in original post

5 REPLIES 5
ChrisMendoza
Resident Rockstar
Resident Rockstar

@MPRGray -

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






Did I answer your question? Mark my post as a solution!
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?

 

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

@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

 

 






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



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] )
)

 

https://dax.guide/groupby/

https://dax.guide/calculatetable/






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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