Forum Discussion
DAX query to create custom table
We had a dashboard in excel. One of the Summary view had values pulled from different source (Pivot table). Some fields were calculated using simple division etc.
I'm trying to replicate the excel view in Power BI. In order to do that, I'm using DAX query to create some static values. The table has only 2 rows. Please see below for example.
Excel
The below table contains sample data. 'Commitment Type' has static values - Funded and Unfunded. The values for Total Losses are being pulled from anoter pivot table using the formula as shown below
Funded GETPIVOTDATA("Sum of funded_losses",'LL Pivots'!$A$7)
Unfunded GETPIVOTDATA("Sum of unfunded_losses",'LL Pivots'!$A$7)
Similarly Balance is pulled from two other columns in the pivot table. The last column 'Percent of total' is calculated using B2/C2 for Funded.
Excel table
A B C D
| Commitment Type | Total Losses | Balance | Percent of total |
| Funded | $1,234,567 | $23,465,863 | 5% |
| Unfunded | $0 | $0 | 0% |
DAX
Using DAX I'm trying to create some static values first as shown below.
CustomTable = DATATABLE( "Commitment Type", STRING, "Total Losses", INTEGER, "Balance", INTEGER,
{
{"Funded", , },
{"Unfunded", , }
})But I'm finding it hard to pull the data for the other columns from a table something like to fill the above blank spaces in the query. Also how do I calculate the in table division?
SUMMARIZE (
Table,
Table[Sum of funded_losses]
)
SUMMARIZE (
Table,
Table[Sum of unfunded_losses]
)
2 Replies
- Greg_Deckler
Community Champion
I would create your datatable but with only a single column, then use ADDCOLUMNS to add your other columns and then you can tie these to just about any calculation.
Hi Greg,
I first created a table using DATATABLE and then tried to ADDCOLUMNS, But I'm unable to perform this operation with this below query. I'm using Summarize to group by a column[INDICATOR] in a table named 'OUT' and then get the SUM(CREDIT_LIMIT).
ADDCOLUMNS(CustomTable, "Total Losses", SUMMARIZE(OUT, OUT[INDICATOR],"Total",SUM(OUT[CREDIT_LIMIT])))
