Forum Discussion

sivarajan21's avatar
sivarajan21
Icon for Post Prodigy rankPost Prodigy
3 years ago
Solved

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 ...
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

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

anushaghi123's avatar
anushaghi123
Icon for Helper II rankHelper II
3 years ago

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!