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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GilesWalker
Skilled Sharer
Skilled Sharer

Table not totalling correctly

Hi everyone,

 

The picture below shows the issue. The graph demonstrates two items of data created using the following measures:

 

Total train capacity = SUMX(SUMMARIZE(Vehicle_Movements,Vehicle_Movements[Wagon and number],Vehicle_Movements[Total wagon capacity]),Vehicle_Movements[Total wagon capacity])

 

Wagon and number is the wagon ID, and total wagon capacity is a calculated column to work out how much a particular wagon can carry, either 1, 2, or 3 units based off of the wagon and number field.

 

The Wagons utilised is calculated as follows:

 

Wagons utilised = SUMX(SUMMARIZE(Vehicle_Movements,Vehicle_Movements[NewColumn.Consignment_Id],Vehicle_Movements[TEU delivered]),Vehicle_Movements[TEU delivered])

 

TEU delivered is a calculated column returning either a 1 or 2 based of a column containing either C40 or C20 (container size).

 

Consignmenr ID is a unique number assigned to each individual container.

 

TEU count.PNG

 

In the picture above the bar graph shows a difference between the black and green bars as 1 (this is what it should show). The table below it is the same data. Each row of the table matches what is in the bar graph however the total for 'Total train capacity' should say 188 not 180.

 

Is there something I am doing wrong?

 

Thanks,

 

Giles

 

1 ACCEPTED SOLUTION

There is not enough information here to confirm if this is a bug or not.  It is very common in DAX that the totals don't add up to the individual row items. Your SUMX doesn't appear to iterate over the Train ID, so it is quite possible this is the problem.  Try re-writing your formula like this (to verify if it is the cause).

 

Total train capacity = SUMX(TableName[Train_ID],SUMX(SUMMARIZE(Vehicle_Movements,Vehicle_Movements[Wagon and number],Vehicle_Movements[Total wagon capacity]),Vehicle_Movements[Total wagon capacity]))

 

Note I don't know what the table name is that contains the train_ID.  you will have to fix that.  Also I am not saying this is a good formula, but it should tell you if the issue is a bug or a bad formula.

 

This blog post is a completely different topic, but it explains the "totals don't add up problem".  http://exceleratorbi.com.au/sum-vs-sumx-in-dax/

 

Sorry if you already know this stuff.



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

12 REPLIES 12
rezaieehsan
Frequent Visitor

hi i have still problems to get currtect column summurization or rowwise sumurization,

please could u quide me how can i get sum in visual level number from each months.

please check the picture attachedInkedseriousQues_LI.jpg

rezaieehsan
Frequent Visitor

hi, i have the same issue, but I want to know how to find out, what does power bi do exactly in the background to calculate the row-wise sum and also column-wise sum

Power BI always does the following.

 

1.  takes a single number from a visual

2. determines what filters are impacting this number from the visuals

3. determins if there are any other filters being used by CALCULATE

4. Combines all filters from 2 and 3, applies these filters to the data model

5. If the tables are joined, the filters propagate in the diretion of the arrows on the relationships

6. The answer is calcualted and return to the visual.

7. Repeat for every cell/number in a visual.

 

A Total Row is exectued in exactly the same way.  There is no concept of totals or subtotals, only filter and then calculate.



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

hi i have somehow same problems to get correct column summurization or rowwise sumurization,

please could u quide me how can i get sum in visual level number from each months.

please check the picture attachedInkedseriousQues_LI.jpg

tnx matt,

how can i perform calcultions on current visual level of matrix data . cause i need just simple arthematic calculations ,regardless of any filter or joins.

 

 

Sorry, I don't understand what you are asking.  Are you saying you want a calcuation that ignores any filters?  In that case you use

 

=calculate([original measure],ALL(TableNameThatNeedsFiltersRemoved))



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

tnx for reply @MattAllington , please check my screenshot, this is my issue , i cant understand how power bi calculated the rowwise subtotal, 

 

 dddd.png

i just want to have sum of the rows in visual level ,i mean only sum of row wise values, as appeared in the visual.cause now the sub total is not the sum when i manually calculate it. 

@rezaieehsan with out understanding the data and how any filters or relationships may be effecting the result, I have had similar issues in the past where the row totals did not match the grand totals. Like Matt answered in the original post sometimes it can be due to how the data is being filtered and what references/relationships are being used.

tnx @GilesWalker i am trying to get it correct but still ,even i removed filters but no results.

GilesWalker
Skilled Sharer
Skilled Sharer

Forgot to mention I have lodged a Frown with the PBI team, and they have lodged a bug report.

There is not enough information here to confirm if this is a bug or not.  It is very common in DAX that the totals don't add up to the individual row items. Your SUMX doesn't appear to iterate over the Train ID, so it is quite possible this is the problem.  Try re-writing your formula like this (to verify if it is the cause).

 

Total train capacity = SUMX(TableName[Train_ID],SUMX(SUMMARIZE(Vehicle_Movements,Vehicle_Movements[Wagon and number],Vehicle_Movements[Total wagon capacity]),Vehicle_Movements[Total wagon capacity]))

 

Note I don't know what the table name is that contains the train_ID.  you will have to fix that.  Also I am not saying this is a good formula, but it should tell you if the issue is a bug or a bad formula.

 

This blog post is a completely different topic, but it explains the "totals don't add up problem".  http://exceleratorbi.com.au/sum-vs-sumx-in-dax/

 

Sorry if you already know this stuff.



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

@MattAllington Thanks for the info. I had a look at the data and you were correct the Train ID was not being taken into account. I looked at the calculated column Wagon and Number which is made up by combining a couple columns together. I added Train ID to the column and this fixed the issue.

 

Thanks,

 

Giles

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.