Forum Discussion
Measure Total Incorrect Oct 2025 Post
Hello,
I have looked at all the blogs and still cannot find the solution for incorrect total. I am using multiple measures. Essentially trying to get future revenue by Manager. I am gettting the correct revenue at the manager level but the total is complete off. here are all my measures. In the screenshot below the total should be $1,955,868 but total is showing $9,726,480. I have copied all the measures. My final measure called "Future Revenue Yearly" which uses 2 other measures
"PVR New Lease Manager" = total revenue per unit for individual manager
"PVR New Lease Avg" = total revenue per unit for all manager
if individual manager revenue is lower then pick revenue for all manager otherwise pick individual manager revenue
PVR New Lease Manager =
VAR mFilter1 =
FILTER('VIN',
'VIN'[Condition] = "New" &&
'VIN'[Sale Type] = "Lease"
)
VAR mFilter2 =
FILTER('Managers',
[Units New Lease] >= SELECTEDVALUE('Units Filter'[Avg Units per Month]) * [Number of Months]
)
RETURN
CALCULATE([Markup], mFilter1, mFilter2)
PVR New Lease Avg =
VAR mAvg =
CALCULATE([PVR New Lease Manager],
FILTER(ALL('VIN'[Manager Name]),
[Units New Lease] >= SELECTEDVALUE('RDR Filter'[Avg RDR per Month]) * [Number of Months]
)
)
RETURN
CEILING(mAvg,1)
Hi arif_ali , this is a classic “incorrect total” DAX issue, and it’s happening because of how your SUMX(SUMMARIZE(...)) expression behaves at the total level.
Why Your Total Is Wrong
Inside the SUMX(SUMMARIZE()), the variable mNewLeaseRev is evaluated in the total context, not row by row for each manager.
When Power BI calculates totals, it removes the row context (no Manager Name context exists), so [PVR New Lease Avg] and [PVR New Lease Manager] both re-evaluate over all managers, which causes the inflated total ($9.7M instead of $1.95M).
In other words, the SUMX loop isn’t doing what you think — it’s summing the same value many times over.
✅ Correct Approach: Use SUMX over DISTINCT Managers with Context Transition
We can fix this by forcing the context transition for each manager inside the loop.
Here’s the corrected version:Future Revenue Yearly = VAR ManagersList = VALUES('VIN'[Manager Name]) RETURN SUMX( ManagersList, VAR mNewLease = IF( [PVR New Lease Avg] > [PVR New Lease Manager], [PVR New Lease Avg], [PVR New Lease Manager] ) VAR mNewLeaseRev = mNewLease * DIVIDE([Units New Lease], [Number of Months]) * 12 RETURN mNewLeaseRev )
Why This WorksVALUES('VIN'[Manager Name]) gives you a distinct list of managers in the current filter context.
SUMX then iterates each manager, and because the row context is converted into a filter context (via context transition), the measures [PVR New Lease Avg], [PVR New Lease Manager], [Units New Lease], etc. are evaluated per manager, not globally.
At total level, Power BI runs the SUMX across all managers, summing the correct per-manager results → giving you the right total.
🧠 Alternative (Simpler) Fix — Wrap the Measure in SUMX Directly
If your model is clean and [Manager Name] is unique per row in 'VIN', you can also do:
Future Revenue Yearly = SUMX( VALUES('VIN'[Manager Name]), VAR mNewLease = IF([PVR New Lease Avg] > [PVR New Lease Manager], [PVR New Lease Avg], [PVR New Lease Manager]) RETURN mNewLease * DIVIDE([Units New Lease], [Number of Months]) * 12 )This version behaves the same but is slightly cleaner.
⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together!🚀 [Explore More]
2 Replies
Hi arif_ali , this is a classic “incorrect total” DAX issue, and it’s happening because of how your SUMX(SUMMARIZE(...)) expression behaves at the total level.
Why Your Total Is Wrong
Inside the SUMX(SUMMARIZE()), the variable mNewLeaseRev is evaluated in the total context, not row by row for each manager.
When Power BI calculates totals, it removes the row context (no Manager Name context exists), so [PVR New Lease Avg] and [PVR New Lease Manager] both re-evaluate over all managers, which causes the inflated total ($9.7M instead of $1.95M).
In other words, the SUMX loop isn’t doing what you think — it’s summing the same value many times over.
✅ Correct Approach: Use SUMX over DISTINCT Managers with Context Transition
We can fix this by forcing the context transition for each manager inside the loop.
Here’s the corrected version:Future Revenue Yearly = VAR ManagersList = VALUES('VIN'[Manager Name]) RETURN SUMX( ManagersList, VAR mNewLease = IF( [PVR New Lease Avg] > [PVR New Lease Manager], [PVR New Lease Avg], [PVR New Lease Manager] ) VAR mNewLeaseRev = mNewLease * DIVIDE([Units New Lease], [Number of Months]) * 12 RETURN mNewLeaseRev )
Why This WorksVALUES('VIN'[Manager Name]) gives you a distinct list of managers in the current filter context.
SUMX then iterates each manager, and because the row context is converted into a filter context (via context transition), the measures [PVR New Lease Avg], [PVR New Lease Manager], [Units New Lease], etc. are evaluated per manager, not globally.
At total level, Power BI runs the SUMX across all managers, summing the correct per-manager results → giving you the right total.
🧠 Alternative (Simpler) Fix — Wrap the Measure in SUMX Directly
If your model is clean and [Manager Name] is unique per row in 'VIN', you can also do:
Future Revenue Yearly = SUMX( VALUES('VIN'[Manager Name]), VAR mNewLease = IF([PVR New Lease Avg] > [PVR New Lease Manager], [PVR New Lease Avg], [PVR New Lease Manager]) RETURN mNewLease * DIVIDE([Units New Lease], [Number of Months]) * 12 )This version behaves the same but is slightly cleaner.
⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together!🚀 [Explore More]Thank you so much for the detailed explanation! It worked perfectly!!!
