Analyze in Excel showing different results than Power BI Desktop

[soundguy2]

I’ve built a Power BI model where one of the measures calculates differently when the dataset is analyzed in Excel.  The measure works correctly when viewed in Power BI Desktop and on the Power BI Service, but when the dataset is exported from the Power BI Service using “Analyze in Excel”, the results are different.  

I’ve confirmed the datasets are identical and that the exact same measures and dimensions are being used.  I saw in another thread that this behavior was due to inconsistent data types, but I haven’t found that in this model.  i.e. I think it should fine to use a combination of Whole Numbers and Decimal numbers in calculations.

The example below illustrates that the total is correct in both cases, but the column values are not correct in Excel.  Has anyone experienced similar strange behavior in Excel?  Any thoughts on what could be the issue?

Correct column values, correct totalIncorrect column values, correct total

[v-frfei-msft]

Hi @soundguy2 ,

Based on my test, I cannot reproduce your issue here. Did you apply any filter in your report in Power BI service?

[soundguy2]

There is a filter for Year and the Group A, B, C shown in the rows, but I've made certain to apply the exact same filters in Excel as in Power BI Desktop and Power BI Service.

[charrington]

Hey guys,

just saw your post cause I was having kind of a similar issue...

I don't have the exact solution, but I noticed that the behavior in Power BI desktop/Service can react a bit differently than in Excel.

For ex. in our case we do have some slicer in the pbix to include or not (0 or 1) some core sales depending on what the users wants to see. Obviously this slicer is only in Service not Excel. And then we have some specific measure that does sales * include or not (0 or 1). For some reason in Excel that slicer field is there but since there are no selection it takes the average by default (0.5) and then I end up with half of my sales in Excel!

To go around that I created two other measures without any include factor, one for regular sales and one for core sales. So now the correct numbers are showing in excel.

So for you case there is probably some different behavior like that...if there is some special DAX in the measure or anything Excel cannot catch up then results might be different. You need to understand the behavior and find your own workaround.

Thanks