Incorrect results of a multiplication of two calculated columns in a table

[Alina12]

Hi guys

I am actually doing a pretty simple calculation and I am multiplying two calculated columns of the same table (Price*Base Value %)

--> Delivered Value = 'Delivered Value'[StoryPrice]*'Delivered Value'[Base Value %]

However, sometimes it doesn't give me the right values by a few decimal places. For example:
Power BI calculates: 5300*9,331%=494,525
correct would be: 5300*9,331%=494,543

Does anyone know what could be the reason for this?

[Sergii24]

Hi @Alina12, the calculation 5300*9,331%=494,543 works correctly in PowerBI (see the evidence below):

Therefore, I assume you multiplicate other numbers.

1. What is a type of "StoryPrice"? Is it "Whole" or "Decimal" number? In case it's decimal, try to change it to whole number
2. What about "Base Value %"?  I suppose it to be a "decimal number" 🙂 PowerBI desktop will allow you to show only 10 decimals, but to verify if it really equals to what you need, you can test it in the following way:
   

Probably you have some "hidden" decimals that impact the result.
I hope it will help! Good luck with your work 🙂

[Alina12]

Hi @Sergii24 

thanks for your reply! 
That was also the first thing I thought of and then tested various data type options. With story price as a whole number and base value % as a decimal number, unfortunately it doesn't work either. Base value % as a percentage does not work either.
I tested your base value check and, interestingly, I only get false here. Don't know why

[Sergii24]

Perfect! So now you know that "base value %" is actually different from the indicated number due to rounding. Now it's up to you to decide what you need: do you want a precise calcualtion? Well, I suppose you already have it. 

Do you want to force PowerBI to use9,331% even if more decimals are present? Then use round funciton in your calcualtion to obtain the desired result.

[Alina12]

Hi @Sergii24 

Ok, now I understand where the difference comes from. However, if I now want to use the rounded version. What is the best way to round? I have just entered the decimal number and the rounding and it gives me the “wrong” numbers?

Should I include a rounding command in my DAX? I only want to use the rounded version, because that way my users can understand the report. Otherwise the “wrong” figures would be there and the recalculation would show the wrong figure.
For example, 0.0933*3400= 317.24 instead of 317.22.
By the way, the totals are correct

[Sergii24]

Hi @Alina12, the option from your screenshot is affecting only the way your data is visualized, but it's not affecting the underlying numbers. To round a number, you need to use Round() fucntion (you can learn more about it here)

Please refer to image below and pbix attached for an example:

I hope it helps! 🙂

[mickey64]

Step 0: I use this 'DATA' table below.

Step 1: I add a calculated columun to the 'DATA' Table.

    Delivered Value = [StoryPrice]*[Base Value %]

Please show me your PBIX-File.

[Alina12]

Hi @mickey64 

unfortunately I can't show you the file because it is confidential. Sorry for that. But as I can say the Base Value % is a lookup value:

Base Value % =

LOOKUPVALUE(

    'Running Total'[Rolling Total (%)],    

    'Running Total'[TeamID], 'Delivered Value'[TeamID],

    'Running Total'[Sprintnumber], 'Delivered Value'[Started in sprint],

    'Running Total'[CompanyID], 'Delivered Value'[CompanyID]          

)

But in the other table the data type is the same then in my actual table

[bhanu_gautam]

@Alina12 , The issue you're encountering with Power BI calculations might be due to the way Power BI handles decimal precision and rounding. Power BI, like many other data tools, can sometimes introduce small rounding errors when performing arithmetic operations, especially with floating-point numbers.

Try using below measure

Delivered Value = ROUND('Delivered Value'[StoryPrice] * 'Delivered Value'[Base Value %], 3)

[dk_dk]

Hi @Alina12 

I suspect it is due to rounding decimals.

Could it be possible that the Base Value % in your calculated column is actually 9,33066%, and Power BI is visually rounding it to 9,331%?

[Alina12]

Hi @dk_dk 

yes that is what happens, because the Base Value % itself is a calculated value (from another table) and not a fixed value 9.331%

[dk_dk]

Then for the Base Value % calculation, you could try to configure it to calculate without rounding, and that should fix the issue.