Forum Discussion
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.
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
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.
12 Replies
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.
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
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.
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.
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 attached
