Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sivarajan21
Post Patron
Post Patron

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:

sivarajan21_1-1694331748947.png

 

Below is the expected correct forecast total:

sivarajan21_0-1694331719244.png

 

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:

sivarajan21_2-1694331942921.png

 

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 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
sivarajan21
Post Patron
Post Patron

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:

sivarajan21_0-1694359510481.png

 

Many thanks for taking this initiative!😊

 

Greg_Deckler
Super User
Super User

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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 

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

anushaghi123_0-1694526687167.png

 

 

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!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.