Forum Discussion
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
| Month | Customers | Days (sum) | Days (avg) | Turn Around % |
| Jul-18 | 13 | 90 | 7 | 2.1% |
| Aug-18 | 8 | 50 | 6 | 2.8% |
| Sep-18 | 4 | 25 | 6 | 2.8% |
| Oct-18 | 8 | 52 | 7 | 2.5% |
| Nov-18 | 5 | 40 | 8 | 1.0% |
| Dec-18 | 4 | 24 | 6 | 3.0% |
| Jan-19 | 3 | 6 | 2 | 3.0% |
| Feb-19 | 9 | 56 | 6 | 2.8% |
| Mar-19 | 17 | 126 | 7 | 1.6% |
| Apr-19 | 30 | 177 | 6 | 3.0% |
| May-19 | 28 | 175 | 6 | 2.8% |
| Jun-19 | 12 | 94 | 8 | 1.2% |
| TOTAL | 141 | 915 | 6 | 2.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 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
- AnthonyTilley
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
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 place
And, in your example, you have to set Turnover's summarization to Average, and not sum.
Thanks
Kaz- AnthonyTilley
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.
Total Value Percentage 5 4 80.0% 6 5 83.3% 7 6 85.7% 8 8 100.0% 9 7 77.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
