Power BI

Creating Time Dimension Summary Table

Synopsis

Many a times, there is need to summarize the values by time dimensions, like Year, Quarter, Month etc. For this we can create a derived table with all required time dimensions and add summary columns. These columns will have summarized values by time dimensions.

Let us assume that we need to create Year time dimension table and summarize various values for each year in this table.

Creating Summary table:

You have a transactional table in your Power BI model that has a column with year of transactional data. From this table, we will extract distinct year values, creating our time dimension summary table. The advantage of creating time dimension table this way is, whenever the data model is refreshed, based on all year values (new and old) of transactional table, years in time dimension summary table will be refreshed as well. In other words, time dimension table will always be updated with all possible years.

Table Command

In data area of Power BI, go to Table Tools and click on New Table icon.

Here in formula bar, you can use summarize command (in its simplest form) to create table. This function use table name that summarized values will come from and column that summarized values will be grouped by,

<new table name> = SUMMARIZE (<transaction table name> , <group by column name>)

Example:

MySummaryTable = SUMMARIZE(TransactionTable, TransactionTable[Year])

The above command will create a derived table called MySummaryTable, based on table called TransactionTable, and a column called Year from this table. The TransactionTable is table with transaction rows.

So, the above command has created a table with just one column, called Year. This column will have all the possible years from transaction table.

Next we will add more columns to this table, where yearly summary values are stored.

Column Command

Select the column, YearCreated, in the new table. Then go to Column Tools in top ribbon. From here select New Column icon.

In the formula bar, DAX command can be specified. Following are few options,

<Column name> = SUM(<table name[column name])

<Column name> = COUNTROWS(FILTER(RELATEDTABLE(<table name>, <condition expression>)

• SUM expression is used to summarize the value from transaction table
• COUNTROWS expression is used to count rows from transaction table, rows that fulfill condition expression

Example:

SumAmount = SUM(TransactionTable[Amount])

This DAX expression will summarize amount column from TransactionTable, into new column of (MySummaryTable in above example) called SumAmount.

CountRows = COUNTROWS(FILTER(RELATEDTABLE(TransactionTable),TransactionTable[Amount])>5000)))

This DAX expression will count rows where Amount is greater than 5000.

Note: all this is good, but not good enough. You will notice so far each row of summary table is showing same amount and count values. Next step will improve the result, and show values just the way we want them.

Link Tables

Linking two tables will make sure that Amount sum and conditional count is done for each value of group (Year).

For this, go to modeling area of Power BI. You will see two table in this area. One is transaction table and other is newly created summary table. Now we need to link these two tables.

Look for the column name that was used to create the table ([Year]). You will notice that this column is in both tables.

Select this column in summary table and drag it over to same column in transaction table. This will link both tables on this column ([Year])

Now when you go back to the data area, you will notice that in summary table, SumAmount column is sum of transaction amount grouped by year.

Similarly, CountRows column is count of amounts greater than 5000 in each year.

Now we have a table that summarizes the values from transaction table and can be used in visualization just like any other table.

Time dimension was just an example taken for this article. You can create summary tables on any dimension in similar fashion.