Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Different values in Excel and Power BI

Hi everyone,

I have ran into a strange problem where I am getting different summarzed results between Excel and Power BI. 

I am importing the following data from Excel; Notice the Turnaround % totals

MonthCustomersDays (sum)Days (avg)Turn Around %
Jul-18139072.1%
Aug-1885062.8%
Sep-1842562.8%
Oct-1885272.5%
Nov-1854081.0%
Dec-1842463.0%
Jan-193623.0%
Feb-1995662.8%
Mar-191712671.6%
Apr-193017763.0%
May-192817562.8%
Jun-19129481.2%
TOTAL14191562.51%

 

Totals are average.

In Power BI, I am importing the same data, nothing fancy, and I am getting the following results

PBI result - Turnaround % different than ExcelPBI result - Turnaround % different than Excel

Days Avg column in Excel has a long decimal value. I have tried a few things like Average X, simple average, rounding to 2 decimal points but nothing seems to please PBI. 

 

Any ideas how to get around this?

Thanks
Kaz

5 REPLIES 5
AnthonyTilley
Solution Sage
Solution Sage

Looking at the data you provided the two datasets do in fact match.

 

the data in your excel file is Formated to percentage 1 decimal place.

when you import into Power BI the full value is imported for example in July the true value is 2.08% in excel this is formated to 1 decimal place Which results in the value being displayed as 2.1%

 

to achive the same result click on the colunm in teh filed list in power BI 

then along the top menu bar click on modeling 

In the formatting section change the Data type to decimal Number 

change the Format to percentage 

change the Decimal places to 1 

 

This will format your table to same as excel 

1dp format.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Hi @AnthonyTilley 

Thanks for the reply and trying it out. 

If I set it to 1 decimal place, it gives me 2.4 instead of 2.5. 

Turnover % with one decimal placeTurnover % with one decimal place

And, in your example, you have to set Turnover's summarization to Average, and not sum.

 

Thanks
Kaz

Hi sorry i was looking at the Values instead of the totals

 

this is actual a common mathamatical problem your total in your excel is not an avarage but a Percentage of the totals. the avarage will usual be diffrent to this figure 

 

for example in the data set below if we divide colunm two by colunm one to get the percentage then the avarage of these 5 Percentage figures is 85.36%(add up all the Percentage figures 426.8% and then devide by 5)  however if we sum the two colunms (35 & 30) and devide these two numbers then the percentage is 85.71% these are two difrent figures.

TotalValuePercentage
5480.0%
6583.3%
7685.7%
88100.0%
9777.8%

 

as you are not importing the individual figures used to derive the % and then using a measure to calculate the avarage value is the closest you can get 

 

inorder to get the figures to match perfectly you would need to import the colunms that are used to calculate the % so that these can be used in a measure to get the total % value

 

hope that makes sense 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Hi @AnthonyTilley 

Thanks for looking at it and replying promptly. I appreicate. 

 

I see where you are coming from, and it makes sense. 

 

I will remove all formatting from Excel and bring in raw numbers and see how they play out. 

 

P.S: I tried with a measure without much joy. It gave me the same results (2.36 & 2.4).

 

Thanks
Kaz

Anonymous
Not applicable

And, one more thing. I am bringing in the individual numbers. Turnaround % is actually calculated based on customers, days sum and days avg, and some other constants. I am awaiting clearance from my busienss user about posting the calculation here. 

 

Thanks
kaz

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.