I have a report using calulated measures which display correctly in Power BI Desktop Report Server (May 2021). When I publish the report to our Report Server web portal and refresh the data, some of the sum totals in the calculated measure seem to be subtracting 1 or rounding down. We have a paginated report that uses the same data source published to PowerBI.com but the totals are correct.
Any help would be appreciated.
4 Comments
- v-robertq-msft
Community Support
Hi,
Unfortunately, I can’t reproduce the same issue as yours.
According to your description, what do you mean by “some of the sum totals in the calculated measure seem to be subtracting 1 or rounding down”. I think you should give the screenshots with the issue so that we can try to find the reason.
Best Regards,
Community Support Team _Robert Qin
The report uses a MS Excel file for this particular calculated measure. I added captions to each screenshot. This is the exact same report, one from the PowerBI Desktop Report Sever (May 2021) and one screenshot from same report loaded in the Report Server Web Portal. Notice the difference in sums.
Notice the difference in total. This is the calculated measure column in the same report within the Report Server web portal.This is the calculated measure column in the same report within Power BI Desktop for Report Server application (May 2021).
I was able to determine the root cause of this but it still may represent a 'bug' of sorts with the May 2021 version of PowerBI Report Server.
The calculated measure in question is pulling data from an Excel file, which has numbers in decimal format. When importing the Excel data into PowerBI via query, the decimals populate. However, the Data Type was set as 'Any'. When the data pulls into PowerBI from the query, the numbers are represented as Whole Numbers, not decimals. The rounding logic seems to work correctly at this point when determining the whole number conversion.
This process seems to break down once the report is published the the Power BI Report Server web portal. The rounding logic does not seem to carry through and this is when we notice differences in our expected sum totals between the version published to the web portal and the version running in the PowerBI desktop application. All decimal numbers seem to round down to the lowest single integer.
The solution was to change the Data Type to 'Decimal' in the query coming from the Excel file. Once this was applied, all logic seemed to carry through once published to the PowerBI Web Portal. The problematic values were rounded correctly in both the application and web portal.
- v-robertq-msft
Hi,
Thank you for the experience sharing!
You can also go to update to the latest version of the Power BI desktop and find more solutions.
Best Regards,
Community Support Team _Robert Qin
