Export Data from Visual Feature Providing Inconsistent Totals

[DavidKett]

Hello, 

When I Export Data from a table visual in Power BI Desktop, the numbers I see as a Total in the Table Visual do not match the numbers I see in Excel when I perform a SUM on the same column of data. 

I have a Sales Amt - YTD measure that is formatted as a whole number in our Tabular model. The total for this column in the example I am using is 155,895 in Power BI Desktop. When I export the data and open in Excel, the total for this column is 155,903. There is obvioulsy some rounding going on during the export process. What is causing Power BI Desktop to recognize the decimal percision of the Sales Amt - YTD measure that is formatted as a Whole Number in Tabular? Why does the Export Data feature round the number when Power BI Desktop does not?

When I change the Data Format of the measure to be Currency or Decimal with two decimal places the numbers are consistent and percise in both Power BI Desktop and Excel. If I change the Currency or Decimal measures to have 0 Decimals in the Tabular model, I get the same result as decribed above, where it is totaling to 155, 895 in Power BI Desktop but the exported data is totaling to 155, 903. 

I would expect these totals to tie in Power BI Desktop and in Excel for the exported data. Please explain the inconsistencies. 

Thanks, 

David

[v-qiuyu-msft]

Hi @DavidKett,

Which formula value do you specify for the measure "Sales Amt -YTD"? 

The Total row within the table visual in Power BI desktop is calculated based on the formula you defined on SSAS side with total row context. While in exported file, the total is sum of each measure value. It depends on how the measure is defined, if it does return the same results in these two scenario. 

Best Regards,
Qiuyun Yu 

[DavidKett]

What do you mean by formula value? DAX code? What measures are refrenced within the DAX code? 

The DAX for Sales - YTD is: 

VAR SelectedCalendar = FILTER('Calendar', 'Calendar'[CalendarPatternSKey] = 'Calendar Filter'[SelectedCalendar]) RETURN CALCULATE(CALCULATE([Sales Amt Input],FILTER (ALL('Calendar'),'Calendar'[YearNumber] = MAX('Calendar'[YearNumber]) && 'Calendar'[Calendar Date] <= MAX('Calendar'[Calendar Date]))),SelectedCalendar) 

I understand that DAX measures inheriet the data type/format from the table columns used to derive the DAX measure, correct?

If this is true, that explains why I am getting a two decimal currency data type. What is confusing is why does the total in Power BI reflect this data type and give me one value, while when I choose to export to excel, Power BI rounds the decimal values up to give me a different number when I total/sum in excel?  Please see earlier post for better explaination of what I mean by this.