Forum Discussion
Row-Level Calculations Correct but Grand Total Incorrect – VM Utilisation
Hi DarthPivotius , Thank you for reaching out to the Microsoft Community Forum.
The screenshot error occurs because SUMMARIZE (or any table builder) was asked to group by a column that isn’t in the input table e.g., you used 'Client Account'[Client Account Chartfield] while the grouping is over Daily Balance. The grand total mismatch happens because you were capping after aggregating across accounts/dates; you must cap at the account×date grain and then sum those capped values so the total equals the sum of the rows.
Use a measure that explicitly builds account×date rows, computes TotalOE and TotalVM for each row, caps there and then sums, for example:
VM Utilisation =
SUMX(
SUMMARIZECOLUMNS(
'Daily Balance'[ClientAccountKey], -- use the account key column from Daily Balance
'Daily Balance'[Date],
"TotalOE", SUM('Daily Balance'[Open Trade Equity]),
"TotalVM", SUM('Daily Balance'[VM Line])
),
VAR TotalOE = [TotalOE]
VAR TotalVM = [TotalVM]
VAR Util = IF(TotalOE < 0, ABS(TotalOE), 0)
RETURN MIN(Util, TotalVM)
)
This guarantees correct row-level capping and accurate grand totals. If your account or date columns exist only in a related table, use the account key present in Daily Balance, or build the virtual table from the related table but use CALCULATE to aggregate values per account×date. I want you to know the measure above is an example only and may require adjustments if your model includes inactive relationships, many-to-many links or other structural details you have not shared here.
DAX overview - DAX | Microsoft Learn
Hi there,
I got the following error below:
Even after fixing this error though the sum came to 21 million when it should be 16 million so I don't think this is correct.
Kind regards,
Luke
Hi DarthPivotius , Thank you for reaching out to the Microsoft Community Forum.
The screenshot error occurs because SUMMARIZE (or any table builder) was asked to group by a column that isn’t in the input table e.g., you used 'Client Account'[Client Account Chartfield] while the grouping is over Daily Balance. The grand total mismatch happens because you were capping after aggregating across accounts/dates; you must cap at the account×date grain and then sum those capped values so the total equals the sum of the rows.
Use a measure that explicitly builds account×date rows, computes TotalOE and TotalVM for each row, caps there and then sums, for example:
VM Utilisation =
SUMX(
SUMMARIZECOLUMNS(
'Daily Balance'[ClientAccountKey], -- use the account key column from Daily Balance
'Daily Balance'[Date],
"TotalOE", SUM('Daily Balance'[Open Trade Equity]),
"TotalVM", SUM('Daily Balance'[VM Line])
),
VAR TotalOE = [TotalOE]
VAR TotalVM = [TotalVM]
VAR Util = IF(TotalOE < 0, ABS(TotalOE), 0)
RETURN MIN(Util, TotalVM)
)
This guarantees correct row-level capping and accurate grand totals. If your account or date columns exist only in a related table, use the account key present in Daily Balance, or build the virtual table from the related table but use CALCULATE to aggregate values per account×date. I want you to know the measure above is an example only and may require adjustments if your model includes inactive relationships, many-to-many links or other structural details you have not shared here.
DAX overview - DAX | Microsoft Learn