discripencies - dax (Power Bi and excel export)

[ashmitp869]

Hi,

I am getting in Power Bi report as 11,127 as total

What when export to excel -

I have used dax query -

Cost = 
CALCULATE(
    SUMX(
        Plan_DailyPlanHoursResourceVersion,
        SWITCH(
            TRUE(),
            Plan_DailyPlanHoursResourceVersion[ResourceTypeDescription] = "Equipment",
                Plan_DailyPlanHoursResourceVersion[ApprovedStdHours] * RELATED(Core_ProjectEquipment[UnitCost]),
            Plan_DailyPlanHoursResourceVersion[ApprovedStdHours] * RELATED(Core_ProjectEmployee[StraightTimeRate])
        )
    )
    ,
    Plan_DailyPlanHoursResourceDetailVersion[IsActive] = TRUE(),
    Plan_DailyPlanHoursResourceDetailVersion[IsLatest] = TRUE(),
    Plan_DailyPlanHoursResourceVersion[DailyPlanStatusDescription] = "Approved"
)

What can be the cause of 5 dollar discripencies ? 

[Deku]

I assume in excel you did a sum of the cell return in the export. The difference is likely due to float data types. Floating point arithmetic on computers will always include some rounding errors, which will always compound (and get larger) the more arithmetic operations you do using the results. In Power BI the total is not calculated from the sum of the subtotal from the table, but is instead calculated independly. Therefore reducing the errors

[ashmitp869]

Hi,

Is there any way to resolve this ?

[Deku · Accepted solution]

Instead of using Decimal (float) use Fixed Decimal

"The Fixed decimal number type is useful in cases where rounding might introduce errors. Numbers that have small fractional values can sometimes accumulate and force a number to be slightly inaccurate. The Fixed decimal number type can help you avoid these kinds of errors by truncating the values past the four digits to the right of decimal separator"

Details on data types in Power BI can be found here