Forum Discussion
Helper IWrong total in automatic sum
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.
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.
Helper IHi,
I upload the file to GitHub :
Do you think i need to put the filter direclty in the dax ?
Thank you very much for your support
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.
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.