Merging grouped rows across multiple tables into new table with DAX

0
votes

I am trying to compiled information from 3 tables of data. Each table has a column with a state abbreviation (STATE), and the second column has a whole number in it denoting the number of accounts my company has in that state (ACCOUNTS). The third column is a number denoting the total balance of the accounts in that state (BALANCE).

This data was queried from 3 databases, each belonging to a dept of our company.

I have the query data loaded into a report in Power BI Desktop, and I know that I need to make a new table, and write some DAX code to sum up the totals from each state, from each table, into one table with all the combined data.

Ultimately I am trying to make a filled map using STATE as the location, ACCOUNTS as the Color saturation, and BALANCE as the Tooltips, functionally creating a heatmap of the US showing where our accounts are most present.

Making the actual map is easy, but my trouble is writing the DAX code to sum up the data across the 3 tables into a new table with the data grouped by state.

1
heatmappowerbibusiness-intelligencedax

1 Answers

0
votes

To achieve the summary table you can create two tables using DAX:

The first one is a simple union of the three:

Table = UNION(Table1, Table2, Table3)

table

(The data is randomly generated so it may not make sense to you, but it should work the same)

The second one is a summary based on the one created above:

Summary = 
SUMMARIZE(
    'Table',
    'Table'[state],
    "accounts", SUM('Table'[accounts]),
    "balance", SUM('Table'[balance])
)

Results:

results