topic Re: PBI summing and rounding or rounding and summing in DAX Commands and Tips

PBI summing and rounding or rounding and summing

Anonymous — Thu, 12 May 2022 22:35:49 GMT

Hi All--

Not sure how to fix this and will very much appreciate the help.

In Power BI is $1.278 vs. our monthly reporting which shows $1,276 from the excel workbook where PBi is pulling the data directly from. Im not sure if Power BI is rounding then summing vs. summing then rounding but that is just a guess.

Thank you

Re: PBI summing and rounding or rounding and summing

amitchandak — Fri, 13 May 2022 01:30:12 GMT

Anonymous , PBI does not round unless that has been done by creating a new column or adding a step in power query .

Or you formula using round before sum

example = sumx(table, round(Table[Value],3) )

You need to download this data and compare it with excel.

orCan you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: PBI summing and rounding or rounding and summing

Anonymous — Fri, 13 May 2022 12:07:21 GMT

Thank you for your response. That column is not using any formula. it's pulling its data directly from the workbook.

Re: PBI summing and rounding or rounding and summing

Anonymous — Tue, 17 May 2022 08:50:56 GMT

Hi Anonymous ,

According to your screenshot, I know the column with problem is in Whole Number Data Type or in Whole Number Format.

Here I create a sample to have a test.

The first matrix show original data in Decimal Number Data Type and in General Format.

The second matrix show data in Decimal Number Data Type and in Whole Number Format.

The third matrix show data in Whole Number Data Type and in General Format.

Result:

We can see that in matrix one Value1 total = 27, in matrix two Value1 total should be 28, however it will still show 27 and in matrix three Value1 total is 28. So we can conclude that if we only change format (whole number) instead of data type, Power BI will sum then round the total. If we change data type (whole number), Power BI will round then sum the total.

Note: change the data type will make the data be stored differently.

So we can understand that modifying the format only modifies the display form of the data, while modifying the data type will directly change the data in data model.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: PBI summing and rounding or rounding and summing

Anonymous — Wed, 25 May 2022 15:11:35 GMT

Hi Rico- I tried playing around with it but still no luck.

This is my value from power query

but it's pulling different value under the report section