CHISQ.INV.RT results are different between Excel 2010, Google Spreadsheet and Power BI...

[paparece]

CHISQ.INV.RT results are different between Excel 2010, Google Spreadsheet and Power BI...

Is this a bug in Power BI?

[v-haibl-msft]

@paparece

Could you please provide a sample calculation? I just have a simple test but get same result in Power BI Desktop, Excel 2016 and Google Spreadsheet as below.

Best Regards,
Herbert

[paparece]

Try these...

CHISQ.INV.RT(0.1, 5.63)

CHISQ.INV.RT(0.2, 5.63)

CHISQ.INV.RT(0.3, 5.63)

CHISQ.INV.RT(0.4, 5.63)

CHISQ.INV.RT(0.5, 5.63)

CHISQ.INV.RT(0.6, 5.63)

CHISQ.INV.RT(0.7, 5.63)

CHISQ.INV.RT(0.8, 5.63)

CHISQ.INV.RT(0.9, 5.63)

CHISQ.INV.RT(0, 5.63)

[v-haibl-msft]

@paparece

I can repro the same issue as you. I’ve reported it internally to Power BI Team: CRI 41126472
I’ll post here once I get any update about it.

Best Regards,
Herbert

[kcproa]

Any resolutions?

[v-haibl-msft]

@paparece @kcproa

I’ve got response from the Product Team.

If the degrees of freedom is not an integer, then

- Excel truncates the decimal places (ROUNDSDOWN). In this example, it will be as-though, the function will be called with that parameter value was 5.

- whereas in DAX we round the number, so if the decimal part is greater than 0.5 it will be rounded up. In this example, it will be as-though, the function will be called with that parameter value was 6.

There is an easy workaround though, the user can always right their DAX calculation in one of the following ways to get results consistent with excel when the degrees of freedom parameter contains a fractional part greater than 0.5.

- CHISQ.INV.RT(0.1, INT(5.63))

- CHISQ.INV.RT(0.1, TRUNC(5.63))

Best Regards,
Herbert