Measure Total Incorrect Oct 2025 Post

[arif_ali]

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

Future Revenue Yearly = 

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

    SUMX(

        SUMMARIZE(

            'VIN', 

            'VIN'[Manager Name]

         ),

        mNewLeaseRev

    )

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)

[arif_ali]

Thank you so much for the detailed explanation! It worked perfectly!!!

[GrowthNatives · Accepted solution]

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 Works

• VALUES('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]