SUMX giving zero total for a column in Table visual

Hi dbattin4 

You need to rewrite the measure so that the total is calculated as a sum of row results rather than trying to compute at the total level.

Option 1: Wrap in SUMX over Opportunities

PreviousMonthValue =
SUMX (
VALUES ( Opportunities_ME[OpportunityNumber] ),
VAR _currentOpp   = Opportunities_ME[OpportunityNumber]
VAR _currentDate  = MAX ( Opportunities_ME[IngestionDate_EOM] )
VAR _prevMonthEnd = EOMONTH ( _currentDate, -1 )
VAR _lastAvailableDate =
CALCULATE (
MAX ( Opportunities_ME[IngestionDate_EOM] ),
Opportunities_ME[OpportunityNumber] = _currentOpp,
Opportunities_ME[IngestionDate_EOM] <= _prevMonthEnd
)
RETURN
CALCULATE (
SUM ( Opportunities_ME[EstimatedValueBase] ),
Opportunities_ME[OpportunityNumber] = _currentOpp,
Opportunities_ME[IngestionDate_EOM] = _lastAvailableDate
)
)

This way, Power BI iterates each opportunity, computes the logic, then adds them up >> totals now match the visible row values.

Option :2 HASONEVALUE fallback

This wraps your original measure but forces the total to re-sum row values:

PreviousMonthValue =
IF (
   HASONEVALUE ( Opportunities_ME[OpportunityNumber] ),
   -- Row-level logic
   VAR _currentOpp   = SELECTEDVALUE ( Opportunities_ME[OpportunityNumber] )
   VAR _currentDate  = MAX ( Opportunities_ME[IngestionDate_EOM] )
   VAR _prevMonthEnd = EOMONTH ( _currentDate, -1 )
   VAR _lastAvailableDate =
      CALCULATE (
          MAX ( Opportunities_ME[IngestionDate_EOM] ),
          Opportunities_ME[OpportunityNumber] = _currentOpp,
          Opportunities_ME[IngestionDate_EOM] <= _prevMonthEnd
      )
  RETURN
      CALCULATE (
          SUM ( Opportunities_ME[EstimatedValueBase] ),
          Opportunities_ME[OpportunityNumber] = _currentOpp,
          Opportunities_ME[IngestionDate_EOM] = _lastAvailableDate
      ),
   -- Total-level fallback: sum of row results
   SUMX (
      VALUES ( Opportunities_ME[OpportunityNumber] ),
      [PreviousMonthValue]
   )
)

This version keeps your structure intact.
At total level >> HASONEVALUE = FALSE >> it re-sums all row results.

It can happen the total is unexpected since it is calculated simply removing filters and not adding up values calculated in different contexts. Anyway, zero is another story. If you scroll down the visual, do you have negatives?

Point is that SUMX is additive, so I doubt that going row by row in the matriux the total will change.

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Hi dbattin4 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you Selva-Salimi FBergamaschi rohit1991 for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

Hi dbattin4 ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

Hi dbattin4 ,

I hope the information provided is helpful.I wanted to check whether you were able to resolve the issue with the provided solutions.Please let us know if you need any further assistance.

Thank you.