Wrong totals, different than export

[Unicorn_Tech]

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?

[Unicorn_Tech · Accepted solution]

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. 

[v-jiascu-msft]

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!
Dale

[Unicorn_Tech · Accepted solution]

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. 

[Anonymous]

Hi, I seem to be encountering the same issue. Would you mind elabloriting on the solution to fix this please.

Thanks in advance.

Kind Regards
Bash

[Unicorn_Tech · Accepted solution]

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.

[v-jiascu-msft]

Hi @Unicorn_Tech,

Thank you for sharing!

Best Regards!

Dale

[dilumd]

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.

[Igotzepowerbi]

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.

[MFelix]

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