Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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
Proud to be a Super User!
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.
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.
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
Proud to be a Super User!
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |