Forum Discussion
Table visual values does not add up to its correct total
Hi,
I have a table visual whose Forecast column values are correct for their months. But its Total doesn't add up when we sum all values.
The visual is shown below whose forecast total 280,000 is wrong:
Below is the expected correct forecast total:
Below is the Forecast dax measure I had created and formatted using Dax studio(Love this tool):
Forecast =
CALCULATE ( SUMX ( VALUES ( Points[DBName-Point_Id] ), [Pre_Forecast] ) )
Pre_forecast =
VAR _Totalunits =
CALCULATE ( SUMX ( Target, Target[Value] ), Target[TargetType] = 0 )
VAR _noofdays =
CALCULATE (
DATEDIFF (
EOMONTH ( MAX ( Calendar_[Date] ), -1 ),
EOMONTH ( MAX ( Calendar_[Date] ), 0 ),
DAY
)
)
VAR _DailyUnits =
DIVIDE ( _Totalunits, _noofdays )
VAR _replaceblank =
IF (
ISBLANK ( [Actual Units] ),
_DailyUnits * _noofdays,
IF ( [Actual Units] = 0, BLANK () )
)
RETURN
_replaceblank
This is my data model:
Is it a dax issue or modelling issue?
Can you please help me out?
PFA file here Portfolio Performance - Live (2).pbix
Please let me know if you need further info!
Thanks in advance!
Measure in totals doesn't return sum of monthly sums, it calculates result for entire period (i.e. year) ignoring row context for each month. I didn't dig details, at least
DATEDIFF (EOMONTH ( MAX ( Calendar_[Date] ), -1 ), EOMONTH ( MAX ( Calendar_[Date] ), 0 ), DAY )
for entire year returns 31 (days in the latest month), not dates of the year.
If you need sum of monthly sums in total, not calculation on entire year, you may calculate forecast like
Forecast = IF ( HASONEVALUE ( 'Calendar_'[Month] ), CALCULATE ( SUMX ( VALUES ( Points[DBName-Point_Id] ), [Pre_Forecast] ) ), SUMX ( VALUES ( 'Calendar_'[Month] ), CALCULATE ( SUMX ( VALUES ( Points[DBName-Point_Id] ), [Pre_Forecast] ) ) ) )don't use VAR in above, most probably will be incorrect result.
To use for Total calculation on entire period or sum results for internal periods depends on your business logic. Since you averaging inside, results could be different assuming DAX code is correct.
In general if you work with Power BI it's much better to discuss that on Microsoft Power BI Community - Microsoft Fabric Community Here are much more people who are working with data modelling and in average they have more experience. Plus Power Pivot works bit differently compare to data modelling in Power BI. And at least you could share .pbix files directly.
3 Replies
Measure in totals doesn't return sum of monthly sums, it calculates result for entire period (i.e. year) ignoring row context for each month. I didn't dig details, at least
DATEDIFF (EOMONTH ( MAX ( Calendar_[Date] ), -1 ), EOMONTH ( MAX ( Calendar_[Date] ), 0 ), DAY )
for entire year returns 31 (days in the latest month), not dates of the year.
If you need sum of monthly sums in total, not calculation on entire year, you may calculate forecast like
Forecast = IF ( HASONEVALUE ( 'Calendar_'[Month] ), CALCULATE ( SUMX ( VALUES ( Points[DBName-Point_Id] ), [Pre_Forecast] ) ), SUMX ( VALUES ( 'Calendar_'[Month] ), CALCULATE ( SUMX ( VALUES ( Points[DBName-Point_Id] ), [Pre_Forecast] ) ) ) )don't use VAR in above, most probably will be incorrect result.
To use for Total calculation on entire period or sum results for internal periods depends on your business logic. Since you averaging inside, results could be different assuming DAX code is correct.
In general if you work with Power BI it's much better to discuss that on Microsoft Power BI Community - Microsoft Fabric Community Here are much more people who are working with data modelling and in average they have more experience. Plus Power Pivot works bit differently compare to data modelling in Power BI. And at least you could share .pbix files directly.
Wow! Amazing solution sir.
This looks absolute beauty and this measure works!
I will accept this as my solution so that it will help others and close this query.
Also, in future I will try the power bi community as well
Many thanks sir!
Excellove15 , you are welcome, thank you for the feedback
