Export full query to Excel/CSV

Hi Anonymous,

I got information below: 

"This is not an actual issue, it is a misunderstanding of how the format is applied.

The format is stored in the model as a property of a column. Whenever that column is shown in a visual (table, chart, matrix, etc) the format is applied. The format is not used when doing computations inside the Analysis Services, where the data is stored.

The actual data stored inside Analysis Services from the attached PBIX is as follows:
Table1[Column1] Table1[Column2]
a 1.74
b 2.86
c 7.42
d 8.36
e 5.23

And the column has a property stored on the model schema: 
           <Property Name="Column2" Type="Double">
              <bi:Property FormatString="0" DefaultAggregateFunction="Sum">

Whenever you execute a DAX query to obtain the value of Column2, the decimal value, with all decimal places will be used. Therefore SUM(Column2) will be 1.74+2.86+7.42+8.36+5.23 = 25.61
So to these values we apply the format "0", meaning we round up, so you get total 26.

However, if you take the rounded up values and add them, you will get 25. This is a matter of adding values with a rounding step applied vs adding values and then applying a rounding step. Those 2 operations are not one and the same and they will produce different results.

This is not an issue of exporting to CSV or exporting to Excel. This is a misunderstanding that the total you see in the visual is the same as adding all the values in the visual, which is not true. The totals we show are always "data" totals and not visual totals. The totals do not have any formatting applied when they are computed, the formatting is applied only once the value is shown to the user, way after it was computed in the Analysis Services. So doing a total over the values exported from CSV, which have the formatting applied, is not the same as the total that is computed by PowerBI to show in the visual.

Therefore there is no actual issue here."

Best Regards,
Qiuyun Yu