Forum Discussion

MPRGray's avatar
MPRGray
Advocate I
7 years ago
Solved

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

Need Help
  • ChrisMendoza's avatar
    ChrisMendoza
    7 years ago

    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/

5 Replies