Forum Discussion
Wrong total in automatic sum
Hello there,
I have an issue with the automatic total/sum in Power Bi.
I export the table in Excel so I can actually see the difference.
The sum of the first column "Rebut à confirmer" is ok.
But the second column "Prix Actuel" display a wrong total, the total in power BI is less than in Excel, in power bi it seems to only taking unique value in the column article (in the column article then can be several time the same article).
Im not sure how to handle this case, any idea, I could workaround this ?
Thank you !
EDIT 1 : I add the .bpix : https://drive.google.com/file/d/1bFjxEGd7x5mCEx9Iy_yQbjevgA4sBkhm/view?usp=sharing
I try to have the right total from the column "Prix Actuel" and "Scrap Cost"
Hi,EFI .Thank you for your reply.
You can't multiply sums directly because Power BI's calculation principle is that when the calculation logic is simple, the system will default to performing the same calculation logic for the Total column that it performs for each of the rows above: multiply the two SUMs that have been aggregated. Because the data in the rows above you is limited by the current row context, they each perform SUM aggregation or display itself, the two themselves after the aggregation of the multiplication does not seem to change!
At this point, the computational environment is:SUM(Current line01 self)*SUM(Current line02 self)The calculated result is equivalent to:
[Current line01]*[Current line02] like this: SUM(2)*SUM(5)=2*5=10But for the Total row, which doesn't exist by itself and is an aggregate value, it performs the above calculation logic by default
It directly multiplies the two totals of the SUM function already executed, which gives the “wrong result”.The right way to do it.
In this case, use SUMX('Table'[column1]*'Table'[column2]).If two fields do not belong to the same table, but there is a relationship between them (not a many-to-many relationship), they can be related using the related function
Buy for your data :Although there is a relationship between these two tables, but the relationship is many-to-many, so you can not use the related function
A better approach is to use the SUMMARIZE and SUMX functions to calculate the sum (for a many-to-many relationship between two tables)
Your two tables, Prix_MAT1 and 'Production, and they are related via Prix Actuel, then the final correct DAX code is as follows:
The steps for this formula are as follows:
A better approach is to use your previously created measure to assist in the calculation via SUMXPrix Actuel DAX = SUMX('Prix_MAT1',Prix_MAT1[Prix Actuel])Calculate with measure[Prix Actuel DAX] with 'Production'[Rebut a confirmer] in the Production table
M_result2 = SUMX('Production','Production'[Rebut à confirmer] * [Prix Actuel DAX])The final calculation result will be 17000 as you expected. I hope my test will be helpful to you
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
14 Replies
Hi,ryan_mayu and Bibiano_Geraldo,thanks for your concern about this issue.
Your answer is excellent!
And I would like to share some additional solutions below.
Hi,EFI .I am glad to help you.
Unfortunately, I cannot access the pbix link you provided due to my work environment.
It shows the following:Can you share your relevant dax code with a screenshot of the corresponding data that is causing the problem, or can you copy the test data from your excel and paste it in the post (be careful not to have private data to avoid data leakage)
Here is my suggestion for your problem.
According to your description, when you are importing data from excel into Power BI Desktop and presenting it directly in visual, when using the system's default aggregation function, the total is displayed incorrectly.
This is in fact a relatively common problem. Sometimes when you use the default aggregation effect of Power BI, part of the current row data and total column data will be calculated or displayed incorrectly.
Generally, the reason for incorrect results in the Total aggregation column is due to the fact that there is no correct calculation environment for the current total row (the calculation environment between the total row and the above data rows is not consistent), or the system has made an error in judgment (this is rare, but it can happen sometimes).
Suggestions:
Try to ensure that the calculation logic of the column data to be displayed in total is simple, without overly complex calculation environment. For example, directly on a column SUM, or AVG, to find the average.
In your case, when the system uses the SUM function and the total value is not correct, you can try to create a new measure, and use the corresponding iterative function SUMX to calculate the result.If you want the total column to be calculated correctly, make the calculation logic simple and use SUMX (a series of iterative functions) to create a single measure instead of the original auto-aggregated column.
URL:
Why Power BI totals might seem inaccurate - SQLBI
Solved: How not to apply cost formula in the Total - Microsoft Fabric Community
Solved: Card total/table total doesn't match with the data - Microsoft Fabric Community
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi,
EDIT : i try to re-upload the file on a non corporate google drive account :
https://drive.google.com/file/d/1bFjxEGd7x5mCEx9Iy_yQbjevgA4sBkhm/view?usp=sharing
I give you a global overview with screenshot then.
EDIT 1 : Sumx giving me the same result as the automatic sum.
Hello,EFI .Thank you for your reply.
Your code seems to be fine, I noticed that your filters area filters the data, have you tried to go over whether the results under these filters are the same as on excel. Please make sure that when checking the data in excel and power bi desktop, the total filter conditions are the same on both sides and try not to set filters conditions.
Please try to disconnect the related relationship in your table model to see if it will affect the result, usually after setting the relationship, the relationship itself will filter the data.
Unfortunately I'm still unable to view your pbix links due to work, you can try to share a link to your github file( pbix file ) so that I may be able to view your pbix file without sensitive data.
Looking forward to your reply.I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
could you pls provide some sample data? What's the DAX coding that you are using?
Hi,
I add the .pbix file on my original post.
Hi, Please make sure that in power bi the summarization was set to SUM as shown bellow:
Hi,
Yes it was by default selected.
