Forum Discussion
Solved
SUMX giving zero total for a column in Table visual
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 _current...
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]) )+0RETURN_ValueLMIf 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.
rohit1991
Super User
Super UserHi 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.
Does not this last version create a circular reference?