Multiplying two measures and losing all decimals

[erikbeauch]

Hi, in the example below, I have measures from a transaction history table.
Quantity is the sum of all share quantities in the history table. 
Avg price is the sum of all costs (Quantity * Paid price per share in a calculated column of this history table), divided by the the total quantity.

Both of them are accurate in the example:

The problem is measuring the Total Cost. When I multiply the Quantity measure (2116) with the AvgPrice measure (30.68384): the total should be 64927.0054, but all decimals are showing as zero. I made sure all data types are set as decimals in Power BI (eg no currencies), for all fields and measures, it makes no difference. I tried applying a Round function to 5 decimals on all measures and fields, makes no difference. Tried using Fixed Decimals, it does not work either. In the source Excel file, and Power Query import, the data type is Number. 

What else can I try? Thanks for your help!

Note: the problem appears to be with the AvgPrice measure, because if I calculate the total cost by using the Quantity measure * harcoded value "30.68384", the result is ok.


I 

[erikbeauch · Accepted solution]

It actually started working by rounding the two sub measures like this:

Mes_AvgPrice = ROUND([Mes_Cost]/[Mes_Qty],5)

[NaveenGandhi]

Hi @erikbeauch 

Just wanted to check if you tried changing the source excel and PQ data types to decimal or fixed decimal?

Regards,
NG

[erikbeauch]

Hi Naveen, in PQ, it's changing the types like this:
type number
Should it be set differently?

[NaveenGandhi]

@erikbeauch 

Type number is the correct data type, Check the below article if it helps in any way.

Also can you share sample file by removing sensitive data to understand whats the issue.

https://www.sqlbi.com/articles/rounding-errors-with-different-data-types-in-dax/

Thanks,
NG

Can you share a sample file by removing sensitive data

[erikbeauch]

Thanks Naveen. Sent you a download link to a sample file with 8 transactions. Thanks for the page, still trying to digest it. Since everything is set as decimal I don't understand yet why the product does not keep those decimals.