Forum Discussion
Row-Level Calculations Correct but Grand Total Incorrect – VM Utilisation
Hello all,
I’m encountering the classic issue in Power BI where a measure works correctly at the row level but the grand total comes out incorrectly.
The goal of the measure is as follows:
For each account and day, if Open Trade Equity is negative, convert it to a positive value.
Cap this positive value at the VM Line (which acts as a maximum limit).
For example, if on a particular day for a given account the open equity is -1.3 million, this should convert to +1.3 million, but then be capped at 1 million due to the VM limit.
Here is my current measure:
VM Utilisation = IF( NOT ISINSCOPE('Client Account'[Client Account Chartfield]), VAR TotalOE = SUM('Daily Balance'[Open Trade Equity]) VAR TotalVM = SUM('Daily Balance'[VM Line]) VAR Utilisation = IF(TotalOE < 0, ABS(TotalOE), 0) RETURN MIN(Utilisation, TotalVM) )This works correctly row by row, but the grand total is not summing correctly.
I have tried some alternative measures suggested in other threads, including:
-- Attempt using SUMX over account values VM Utilisation = SUMX( VALUES('Client Account'[Client Account Chartfield]), VAR TotalOE = CALCULATE( SUM( 'Daily Balance'[Open Trade Equity] ) ) VAR TotalVM = CALCULATE( SUM( 'Daily Balance'[VM Line] ) ) VAR Util = IF( TotalOE < 0, ABS( TotalOE ), 0 ) RETURN MIN( Util, TotalVM ) )and
-- Attempt using SUMMARIZE VM Utilisation = VAR Accounts = SUMMARIZE ( 'Client Account', 'Client Account'[Client Account Chartfield] ) RETURN SUMX ( Accounts, VAR TotalOE = CALCULATE ( SUM ( 'Daily Balance'[Open Trade Equity] ) ) VAR TotalVM = CALCULATE ( SUM ( 'Daily Balance'[VM Line] ) ) VAR Util = IF ( TotalOE < 0, ABS ( TotalOE ), 0 ) RETURN MIN ( Util, TotalVM ) )Unfortunately, neither of these alternatives worked as expected — when multiple accounts are selected, the measure does not sum correctly.
I’m looking for a measure that:
Works correctly row by row.
Correctly aggregates for grand totals and multi-account selections.
Any guidance or working solution would be greatly appreciated.
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
5 Replies
VM Utilisation =
SUMX(
SUMMARIZE(
'Daily Balance',
'Client Account'[Client Account Chartfield],
'Daily Balance'[Date]
),
VAR TotalOE = CALCULATE(SUM('Daily Balance'[Open Trade Equity]))
VAR TotalVM = CALCULATE(SUM('Daily Balance'[VM Line]))
VAR Util = IF(TotalOE < 0, ABS(TotalOE), 0)
RETURN
MIN(Util, TotalVM)
)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
Hi DarthPivotius , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.
Hi DarthPivotius , Hope you're doing fine. Can you confirm if the problem is solved or still persists? Sharing your details will help others in the community.
