SUMX giving zero total for a column in Table visual

[dbattin4]

I am looking at a table visual where the line totals are correct but the Grand Total is coming back as zero despite using SUMX

The measure is as follows

PreviousMonthValue =

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]), FILTER( ALL(Opportunities_ME), Opportunities_ME[OpportunityNumber] = _currentOpp && Opportunities_ME[IngestionDate_EOM] <= _prevMonthEnd ) )

VAR _ValueLM = CALCULATE(

     SUMX( FILTER( ALL(Opportunities_ME), Opportunities_ME[OpportunityNumber] = _currentOpp && Opportunities_ME[IngestionDate_EOM] = _lastAvailableDate ),Opportunities_ME[EstimatedValueBase]) )+0

RETURN

_ValueLM

Anyone know why?

[v-venuppu]

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.

[v-venuppu]

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.

[Selva-Salimi]

Hi @dbattin4 , try this updated measure:

VAR _currentOpp = VALUES(Opportunities_ME[OpportunityNumber])

VAR _currentDate = MAX(Opportunities_ME[IngestionDate_EOM])

VAR _prevMonthEnd = EOMONTH(_currentDate, -1)

VAR _lastAvailableDate = CALCULATE( MAX(Opportunities_ME[IngestionDate_EOM]), FILTER( ALL(Opportunities_ME), Opportunities_ME[OpportunityNumber] IN _currentOpp && Opportunities_ME[IngestionDate_EOM] <= _prevMonthEnd ) )

VAR _ValueLM = CALCULATE(

     SUMX( FILTER( ALL(Opportunities_ME), Opportunities_ME[OpportunityNumber] IN _currentOpp && Opportunities_ME[IngestionDate_EOM] = _lastAvailableDate ),Opportunities_ME[EstimatedValueBase]) )+0

RETURN

_ValueLM

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[FBergamaschi]

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

[rohit1991]

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.

[dbattin4]

Does not this last version create a circular reference?