Forum Discussion
Extraction from Table visual to Excel Giving errors in total showing in excel when manualy counted
Issue While extracting to excel i have used Export data with current layout.
when i check total in powerbi for sales field in table visual it is giving me a total of 449345030.2737 and the same total in excel but when i add all rows in excel manually it is giving the sum(441701970).
what might be the issue can anyone help me on this its very urgent
thanks,
Hi Saurabhuu ,
Thanks for your update. Since the issue is still occurring, let's check a few more things that might be affecting the total.
First, please verify if any report, page, or visual-level filters are applied in Power BI. Sometimes, the table visual may show a filtered version of the data, which could lead to differences when exporting. Hidden data, summarization, or aggregation logic might also impact the results.
Another factor could be how the measure is calculating the total. While SUM(Sales) should aggregate the column directly, implicit filters or calculations may be affecting the final total. As a test, try creating a calculated column instead:
Sales_Check = Table[Sales]
Then, sum this column in Power BI and compare it with the Excel total to see if there's a difference.
If the issue persists, it would be helpful if you could share PBIX file (or a version with sample data) along with the exact DAX measures used. This will allow us to take a closer look and provide more specific guidance.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and a kudos would be appreciated.
Regards,
Vinay.
10 Replies
Assume you are summing a fixed decimal number
The Fixed decimal number type is useful in cases where rounding might introduce errors. Numbers that have small fractional values can sometimes accumulate and force a number to be slightly inaccurate. The Fixed decimal number type can help you avoid these kinds of errors by truncating the values past the four digits to the right of decimal separator.
In power bi the total is calculated independly, as are each row in a table. Then you sum in excel you are compounding the small differences resulting in different totals
Try swapping to a decimal data type
If there is decimal issue it should not give me thatmuch difference. And however i check the decimal places also.can you give me the any other solution.
Thanks,
Hi Saurabhuu ,
Thanks for using Microsoft Fabric Community,In addition to the steps suggested by Deku please consider the following:
Since you are using SUM(Sales), Power BI should be performing a standard column aggregation. However, to further troubleshoot the discrepancy, please try the following:
- If you exported using "Data with Current Layout", try exporting with "Underlying Data" instead to ensure Excel receives raw, unaggregated values.
To use this Report author should enable "Allow end users to export with underlying data" in Desktop and republish the report.
Please refer this for more details: Export data from a Power BI visualization - Power BI | Microsoft Learn
- Compare the total number of rows in Power BI vs. the exported Excel file to check for missing or extra records.
-
If the issue persists, try using the following DAX measure to verify row-level summation:
SUMX(Table,Table[Sales]).
This explicitly sums each row’s value, ensuring no unexpected aggregation differences.
Please try these steps and let us know if the discrepancy still exists.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and a kudos would be appreciated.
Regards,
Vinay.I have tried all the possible ways but still iam facing the same issue
Hi Saurabhuu ,
Thanks for your update. Since the issue is still occurring, let's check a few more things that might be affecting the total.
First, please verify if any report, page, or visual-level filters are applied in Power BI. Sometimes, the table visual may show a filtered version of the data, which could lead to differences when exporting. Hidden data, summarization, or aggregation logic might also impact the results.
Another factor could be how the measure is calculating the total. While SUM(Sales) should aggregate the column directly, implicit filters or calculations may be affecting the final total. As a test, try creating a calculated column instead:
Sales_Check = Table[Sales]
Then, sum this column in Power BI and compare it with the Excel total to see if there's a difference.
If the issue persists, it would be helpful if you could share PBIX file (or a version with sample data) along with the exact DAX measures used. This will allow us to take a closer look and provide more specific guidance.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and a kudos would be appreciated.
Regards,
Vinay.
- If you exported using "Data with Current Layout", try exporting with "Underlying Data" instead to ensure Excel receives raw, unaggregated values.
