Forum Discussion
Multiplying two measures and losing all decimals
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
It actually started working by rounding the two sub measures like this:
Mes_AvgPrice = ROUND([Mes_Cost]/[Mes_Qty],5)
5 Replies
It actually started working by rounding the two sub measures like this:
Mes_AvgPrice = ROUND([Mes_Cost]/[Mes_Qty],5)
NaveenGandhiMemorable Member
Hi erikbeauch
Just wanted to check if you tried changing the source excel and PQ data types to decimal or fixed decimal?
Regards,
NGHi 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