Forum Discussion
Wrong totals, different than export
Hi. I have a table in Power BI, that looks pretty good. See pic below. The problem is on export, the totals in excel are different. See excel photo at bottom. So basically the excel document is returning the correct totals, but for some reason the Power BI totals are off for the actual presented data. How to fix this?
I actuall was told by our developer that the problem had to do with summarizing LOV values. We made this change, and the problem resolved itself.
Hello, I apologize for the delay. What solved it for us was a simple rounding - surrounding the formula calculating all the numbers with the round formula, to two decimal points.) This allowed all the sums to add up correctly.
8 Replies
Hi Unicorn_Tech,
Power BI pivot table are slighty diffenrent in terms of context than the excel so if you are calculating averages, distintct counts or using measures in your pivot the calculations are made on the aggregated level and not on the line level.
For example I supposed that in the count of name you are using a distinctcount and not a simple count correct? When you do this the total line will make the distinct count on all the levels and not the sum of de different year:
Example: Result:
2013 - A 2013 - 1
2013 - A 2014 - 2
2014 - A 2015 - 1
2014 - B Total - 2
2015 - A
What the calculations is making is the distinct count of the names A/B and in total there are only two distinct names.
Check if your total values have some issues of context within your table.
Regards,
MFelix
Hi
This is possibly due to filtering and summation of your data when there is a relation build between tables.
Please create your measure as follows to get the correct values.
IF(HASONEVALUE(TableName[FieldName]),[Total Amount field],SUMX(VALUES(TableName[FieldName]),[Total Amount field])).
If you share some sample data i can do it for you.
Could you help me write your formula? I am having the exact some problem where my totals in PBI do not match the excel export.
Hi Unicorn_Tech
Could you please mark the proper answer if it's convenient for you? That will be a help to others.
Best Regards!
DaleI actuall was told by our developer that the problem had to do with summarizing LOV values. We made this change, and the problem resolved itself.
