Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

chrisbrigg

Calculated measure sum changing after Power BI Report Server refresh

Submitted by on ‎07-03-2021 04:20 AM

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.

Status: New
See more ideas labeled with:
4 Comments (4 New)
Comments
v-robertq-msft
Community Support
Community Support
‎07-05-2021 12:30 AM

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

chrisbrigg
Frequent Visitor
‎07-06-2021 04:47 AM

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.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).This is the calculated measure column in the same report within Power BI Desktop for Report Server application (May 2021).

chrisbrigg
Frequent Visitor
‎07-06-2021 10:31 AM

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.

datatypeany.PNG 

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.

datatypedecimal.PNG

v-robertq-msft
Community Support
Community Support
‎07-06-2021 07:05 PM

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

Idea Statuses