Wrong total in automatic sum

[EFI]

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"

[Anonymous]

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.

[EFI]

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.

[Anonymous]

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.

[EFI]

Hi,

I upload the file to GitHub :

Solfcast/Production_sample

Do you think i need to put the filter direclty in the dax ?

Thank you very much for your support

[Anonymous]

Hi,@EFI .Thank you for your reply.

Luckily, I was able to get your files through the github link now. I noticed that you have set up a series of filters in the filters area of power BI Desktop, can you explain in detail the filtering logic of this setting, such as what exactly are your filters and what kind of data you want to select?

It would be nice if you could similarly share your excel test data by way of the same github (I'd like to know what your filters are for correct data presentation in excel)
Could you also share your excel file (without sensitive data)?

For your follow-up idea of applying a bit of your filtering logic in dax, I think it's feasible to show more visually what your filtering logic is.

Again, thank you very much for your response and I look forward to hearing from 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

[EFI]

There is only 3 filters i need.

Date : Just exculte blank.

ImportDate : Every month we import new price (Prix_MAT1 table), I need to show only last updated.

Description : Just filter out some "type" not relevant to show in the table

All table come from SQL, the excel is just the export from the table display on the PBIx.

It was just a way for me to manually check the total.

[Anonymous]

Hi,@EFI .Thank you for your reply.

In fact if the connectivity of your data model is indeed correct
Calendrier [Date] - Production [ImportDate]
Production [Article] - Prix_MAT1 [Article]
In my tests the data should indeed be fine!
Because for the same data (in the column article then can be several times the same article) power BI may aggregate the exact same data, but it does not affect the final calculation of the total rows, I checked your data and I did not find any miscalculations!

You need to make sure that you apply the value filters filtering environment is indeed correct, I think the filtering environment error is the cause of the data does not display properly an important reason (in the case of the model relationship filtering logic is correct)
At the same time, please double-check the data source (database filter conditions are consistent with Power BI)

When you choose to filter the data for the date table 10/01/2024

NumTestlastDate = 
VAR _leastdate=CALCULATE(MAX('Prix_MAT1'[ImportDate]),ALL(Prix_MAT1))
RETURN 
     CALCULATE(SUM('Prix_MAT1'[Prix Actuel]),FILTER(ALL('Prix_MAT1'),Prix_MAT1[ImportDate]=_leastdate))

Select to filter the data of date table 10/01/2024, because the relationship of Production[Article] - Prix_MAT1[Article] is many-to-many, and Prix_MAT1[Article] does not have a blank value, so filter out the data of Article= blank.

I hope the following links to relevant checking model data will help you
URL:

Solved: Relationship query - 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.

[EFI]

Oh alright, seems accurate now, thanks !

Would you also have a suggestion in DAX to multiply two column ?

I try to do a simple sum x sum but its not working as expected for the total.

The value in "Total" column should be around 17 000 €

I think power BI is calculating the total from both "Prix Actuel" and "Total" column to get the 2 344 775,99 €

[Anonymous · Accepted solution]

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=10

But 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 SUMX

Prix 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.

[EFI]

 @Anonymous I see, thank you for your explaination behind Power BI logic calculation, all seems good for me now. thank you for your time !

[Bibiano_Geraldo]

Hi, Please make sure that in power bi the summarization was set to SUM as shown bellow:

[EFI]

Hi,

Yes it was by default selected.

[ryan_mayu]

could you pls provide some sample data? What's the DAX coding that you are using?

[EFI]

Hi,

I add the .pbix file on my original post.