cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
quads08
New Member

Paginated Reports DIV/0 Error

Hello everyone , I'm new to power BI report builder and have been able to google most things. However I am stuck here. 

 

I use this expression and it works 99% of the time except when Fields!ID_Last_4_Weeks_Previous_Year_ECNs_.Value is blank. 

 

Is there a way to modify this to capture that error and keep everything else intact?

 

=IIf(Sum(Fields!ID_Last_4_Weeks_Difference_ECNs_.Value) = 0 ,"",Sum(Fields!ID_Last_4_Weeks_Difference_ECNs_.Value/4)/Sum(Fields!ID_Last_4_Weeks_Previous_Year_ECNs_.Value/4))

 

 

5 REPLIES 5
v-jingzhang
Community Support
Community Support

Hi @quads08 

Have you solved this problem? If not, can you share some dummy data so that I can use it to find possible solutions? Thank you.

 

Regards,
Community Support Team _ Jing

@v-jingzhang 

No I am still stuck. How can I share the dummy data?

You can upload the file to some cloud storage platform like OneDrive or Google Drive and share the link here, so that we can download it. Remove sensitive info before sharing.

 

Additionally, I found some topics about this same error related to SSRS, maybe they can help.

https://stackoverflow.com/questions/21552118/ssrs-expression-the-value-expression-for-textbox-has-sc...

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/708b9ece-b631-4a11-90b1-f867c943040f/report...

 

Expression examples:

https://docs.microsoft.com/en-us/power-bi/paginated-reports/report-builder-expression-examples#Decis...

 

PhilipTreacy
Super User III
Super User III

Hi @quads08 

Always use DIVIDE() to avoid Div by 0 errors.  ANywhere you do division replace it with DIVIDE e.g.

DIVIDE( DIVIDE( Sum(Fields!ID_Last_4_Weeks_Difference_ECNs_.Value, 4) , DIVIDE( Sum(Fields!ID_Last_4_Weeks_Previous_Year_ECNs_.Value, 4))

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Good morning @PhilipTreacy ,

First off thanks for the quick reply! I tried to use that but it doesn't work. It gave me the following error.workingworkingnot workingnot workingPBI report builder error.JPG

 

The Value expression for the textrun 'Textbox10.Paragraphs[0].TextRuns[0]' has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset.
----------------------------
The definition of the report '' is invalid.
----------------------------
An error occurred during local report processing.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors