Creating a LIVE Material Takeoff report in Power BI, based on your active Revit model.

Starting from version 1.25, RTBI exposes information and quantities of materials from your live Autodesk® Revit® model. To illustratie this, in this tutorial we will use RTBI to connect with a Revit model and extract its material-quantities data. We will link prices to these materials and interact with them through a Microsoft® Power BI® dashboard. You can watch the tutorial videos and read through the next sections where we explain how to do this step by step.

Overview:

1. Create RTBI connection
2. Extract data tables
3. Connect prices table
4. Add relations
5. Add calculation columns
6. Create RTBI dashboard
7. Interact with RTBI data

1 – Create RTBI Connection between Revit and Power BI

To get started, you must open a Revit model, launch the RTBI service and connect a Power BI report.

1. Open your Revit project.
   
2. Go to Add-Ins and click the [RTBI] icon.
   A window will appear where you can launch the RTBI server.
   
3. Next you can select the element collection to export.
   For this tutorial we will select the [View Independent Instances].
   
4. Open Power BI and create a new [Blank Report].
   
5. Click [Get Data] and connect to the web-server.
   Select web, click connect and enter the url (http://localhost:19723).
   
6. Select the correct data encoding type (UTF-8).
   Click [Transform Data].
   
7. Set the source file-type as JSON.
   
8. For best practice, rename the connected data source as root-RtbiExport.
   The data source contains 5 tables, some of which we will use in the next sections.

2 – Extract the necessary data

After establishing an RTBI connection, you can extract different data tables. We will extract tables for the present Elements, used Materials and ObjectMaterialUsages. The ObjectMaterialUsages contains usage quantities for each material and its related elements.

1. First, create the Elements table. Create a new reference to the root-RtbiExport. Rename it to the table you want to extract, in this case “Elements”.

2. Click the Elements entry data type [List]. This will navigate you to the exported Elements data.

3. Convert the data to a table by clicking [Convert To Table]. This will give you a table with all element objects.

4. Next we want to expand some of the element properties to columns. For this we will expand columns for Id, CategoryName and Property.Name. Click the expand [columns] icon. Select 

• • • Id
    • CategoryName
    • Property.Name

If you use the filter to select columns, make sure to clear the filtering after selection, else you will only select the current visible properties.

5. Repeat the previous to extract a Materials table. For the Materials table columns we will use the properties

• • • Name
    • ElementId
    • Id.

6. Repeat the previous to extract a ObjectMaterialUsages table. For the ObjectMaterialUsages table columns we will use all properties present on a ObjectMaterialUsages entry. These are

• • • MaterialId (a project specific material id)
    • ObjectId (the related element id)
    • AreaSqft (the area value in revit’s internal area unit (square feet))
    • AreaCalculated (the area value in the user’s area unit)
    • AreaCalculatedUnit (the area unit name of the user’s area unit)
    • VolumeCft (the volume value in revit’s internal volume unit (cubic feet))
    • VolumeCalculated (the volume value in the user’s volume unit)
    • VolumeCalculatedUnit (the volume unit name of the user’s volume unit).

7. Check and convert the data types for each column in each table. It is important to use the correct data types for all values. In this case we will convert all extracted ObjectMaterialUsages quantities to Decimal Numbers. This will make sure Power BI will correctly calculate the necessary totals in the next steps. Convert the following to Decimal Numbers

• • • AreaSqft
    • AreaCalculated
    • VolumeCft
    • VolumeCalculated.

3 – Connect with Unit Prices data

To add unit prices to our materials, we will connect a MaterialPriceInfo table. For this tutorial we will use a Google Sheets spreadsheet with random generated data. In your real life scenario you can connect your prices to the revit model materials using the unique Material.Name key, or any other Unique Identifier.

1. Observe the Materials table. This table contains all exported materials from the revit model. We will use this data to create the MaterialPriceInfo table.

2. Create a new Google Sheet, name it MaterialPriceInfo. Also rename the sheet’s tab to MaterialPriceInfo.

3. Copy and paste the entire Materials table to the new MaterialPriceInfo table.

4. Create 3 extra columns to add price information.

• • Price
  • UnitType
  • UnitTypeId

5. We will use Google Sheets functions to generate the price information and use this extra information to easily calculate, navigate and interact with the Power BI dashboard.

6. In the Power Query Editor click [New Source] to connect the Google Sheet we just created. In the Get Data prompt select [Google Sheets] and click [Connect].

7. Next, enter the Google Sheet URL.
You will be prompted to sign in to Power BI with your google account.

8. In the Navigator prompt, select the Google Sheet tab and click [OK].
The Google Sheets table will be loaded into Power BI.

9. As a last step, promote the table first row to table-headers and convert the Price column data type to Decimal Numbers.

4 – Setup relationships

For Power BI to link related data, we need to add some relations between the extracted tables.

In the Power Query Editor, click [Close & Apply].
Your queries will be loaded into Power BI.

Navigate to the [Model view] and add the following relations.
Make sure to set the Cross-filter direction to [Both].
MaterialPriceInfo : Materials
1 : 1
Name : Name

Materials : ObjectMaterialUsages
1 : *
Id : MaterialId

Elements : ObjectMaterialUsages
1 : *
Id : ObjectId

5 – Calculate derriven information

Now when we have added relations between the tables, we can easily create calculation columns with DAX. We will enrich the ObjectMaterialUsages table with a calculated PriceTotal for each element. For better clarity, we will also add a UnitType and a Price column.

1. In Power BI, navigate to the [Table view] and select the ObjectMaterialUsages table.

2. Click [New column] and enter the DAX formula to calculate the data for each table record. Check out the tutorial video for the required formulas and add

• UnitType
• Price
• PriceTotal

6 – Create RTBI dashboard

Everything is now set up to create a custom dashboard. For this tutorial we will use 2 Power BI visuals and the RTBI visual to interact with the actual revit model itself in real time.

Add a [Matrix] visual. Fill the buckets with these fields.
Rows
Materials.Name
Elements.Name

Values
ObjectMaterialUsages.PriceTotal

Columns
ObjectMaterialUsages.UnitType

Add a [Stacked bar chart] visual. Fill the buckets with these fields.
Y-axis
Materials.Name

X-axis
ObjectMaterialUsages.PriceTotal

Add the RTBI visual to connect with the revit model
When you install the RTBI extension, a .pbiviz file is installed with it. This is the Power BI visual to select and load in the next prompt. You can find the location of this file through a link in the RTBI extension dialog.

Open the extension dialog and select the [?] “how to” questionmark to navigate to the “how to” section. Next click [OPEN DIRECTORY WITH EXAMPLES] to open the file location where the Power BI visual (.pbiviz) file is installed.

When you know the .pbiviz file location. Go back to Power BI, in the visualizations pane click [Get more visuals] > [Import a visual from a file]. Navigate to the .pbiviz file location and open the file. The RTBI visual will now load into the Power BI visualizations pane.

Add the RTBI visual. Fill the buckets with these fields.
Highlight/Filter Ids
Elements.Id

Color By
ObjectMaterialUsages.UnitType

The RTBI visual provides you with 3 buttons to interact with the revit model
Highlight Elements
Isolate Elements
Colorize Elements

Select a Material in the Power BI dashboard, and click the RTBI button [Highlight Elements]. The related elements will be highlighted in the active revit model.

7 – Interact with RTBI data

Everything is now set up for analyzing and interacting with the revit model through the RTBI connection. In this case, you can for example analyze price totals for each used Material in the project. You can select a Material in the Power BI dashboard, view its total-material-usage-price and select the related elements in the revit model.

Activate different data elements in the Power BI dashboard and select, isolate or colorize them with the RTBI visual buttons.