Table visual values does not add up to its correct total

[sivarajan21]

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!

@Ahmedx @amitchandak @Greg_Deckler @grantsamborn @Ashish_Mathur @Idrissshatila @Dhairya 

[Ashish_Mathur]

Hi,

In that file, the table that you showed in the first image does not exist at all.  Please atleast share the table where the problem can be seen.  Share a smaller sample file please.

[sivarajan21]

Hi @Greg_Deckler ,

Many thanks for your quick response sir!

I will have a look into this and get back

As of now, I have voted the idea and gave a comment as shown below:

Many thanks for taking this initiative!😊

[Greg_Deckler · Accepted solution]

@sivarajan21 First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[sivarajan21]

Hi @Greg_Deckler ,

Absolutely fantastic!

This work like a gem! Also, I have noted it down for future use

Accepted yours as a solution

Thanks 

[anushaghi123]

Hi,

I have created the quick measure to calculate the moving average with dynamic slicer. But facing column total error for the measure. Like it has to sum Jan to Dec data and show it in the column but column total only taking Dec month data everytime.

Example - Should be Oct+Nov+Dec = 75379+62685+26346 = 164410 But the total showing is 26346.

Please help me with the following issue

I am getting column total error and taking Matrix table 

Rows - Country,ProductCategory

Column - Month

I'm getting column total incorrect for Forecast(Blue) data.

Dax Formula (Quick Measure) - Product_Count rolling average =
IF(
ISFILTERED('Table_name'[dimdate]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __LAST_DATE = ENDOFMONTH('Table_name'[dimdate].[Date])
VAR __DATE_PERIOD =
DATESBETWEEN(
'Table_name'[dimdate].[Date],
STARTOFMONTH(DATEADD(__LAST_DATE, 'Moving Average'[Moving Average Value], MONTH)),
__LAST_DATE
)
RETURN
AVERAGEX(
CALCULATETABLE(
SUMMARIZE(
VALUES('Table_name'),
'Table_name'[dimdate].[Year],
'Table_name'[dimdate].[QuarterNo],
'Table_name'[dimdate].[Quarter],
'Table_name'[dimdate].[MonthNo],
'Table_name'[dimdate].[Month]
),
__DATE_PERIOD
),
CALCULATE(
SUM('Table_name'[Product_Count]),
ALL('Table_name'[dimdate].[Day])
)
)
)

Thanks!