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.
Helper IThere 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.
- 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 !
- 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 €
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.