Hi,
Can someone explain me the reason why SUMX function is showing different result ?
Thanks.
1 Like
Hey,
Something is wrong.
I will suggest you to check data type of both the col
check is their any filter applied.
or try to replicate same table in other page with same formula
@Bq1BQfLi Just do a COUNTROWS ( Online Retail ) and you will see the count of underlying rows that make up the Total Revenue Calculation, Matrix is showing unique values for the 3 columns but SUMX & DAX work in Filter Context & Row Context, so you need to understand there will be duplicated rows in the online retail due to similar transactions.
Here you are assuming that there is only 1 row for ( 14-11-2011 / 1 / 0.38 ) and that’s not the case for sure.
2 Likes
Just as @AntrikshSharma said, it seems pretty clear that you have multiple rows with the same InvoiceDate values. It doesn’t appear that way on the surface because you have a “1” in the Quantity column, but it is likely that incorrect data type and/or unexpected aggregation settings on the Quantity column are causing that.
Hi @AntrikshSharma & @DaveC
Thanks for your quick insights on this issue. Up on your suggestions I have looked into my data and I found duplicates in it.
Here is the snapshot
But SUMX do row-by-row calculations and iterates through every row of a specified table to complete the calculation why is it summing when we have duplicates in the columns ?
Your thoughts on this is really helpful.
Thanks.
I think the first question that should be answered is “Why do you have duplicate rows?” If you can remove the duplicates in Power Query, most if not all of your problems should resolve themselves. Also, I would recommend adding an InvoiceNo column to your table to avoid any invoices with the same InvoiceDate from being aggregated into a single row.
3 Likes
Iterators do Row by Row calculations and then aggregate the intermediate result of row by row calculation. 2.95 * 1 + 2.95 * 1 = 5.9 rounded off to 6.
1 Like