Table visual values does not add up to its correct total

[Excellove15]

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!

@Sergei Baklan 

[#2 · Accepted solution]

@Excellove15 

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]

@Sergei Baklan 

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!

[#4]

@Excellove15 , you are welcome, thank you for the feedback