Forum Discussion

NJ81858's avatar
NJ81858
Helper IV
3 years ago

Report Builder Error

Hello,

 

I have a calculated field in Power BI Desktop that I am trying to match in Power BI Report Builder and it is working for some categories but not for others, and I'm not sure either where I am going wrong, or where I can find more information about the error.

 

This is what the column looks like in Power BI Desktop:

 

 

The calculation in question is just a weighted tax rate, multiplying the purchase amount by the tax rate and dividing by the purchase amount for each location. 

 

Here is the measure in Power BI Desktop:

Weighted Rate = 
VAR _num = CALCULATE(SUMX('Table','Teable'[Amount]*'Table'[Rate]), FILTER('Table', [Location] = [Location]))
VAR _denom = CALCULATE(SUMX('Table', 'Table'[Amount]), FILTER('Table', [Location] = [Location]))

RETURN 
DIVIDE(_num, _denom) / 100

 

Here is the expression in Report Builder:

( Sum ( ( Fields!Amount.Value *Fields!Rate.Value ) ) / Sum ( Fields!Amount.Value)) / 100

 

When I run the report this is the result:

 

 

I just am not sure why I am getting an error in some instances and not others. For the calculations that do show in Report Builder, they are correct so I am not sure how to proceed. Thank you in advance!

Power BI report builder

1 Reply

  • d_gosbell's avatar
    d_gosbell
    Super User
    3 years ago

    The DIVIDE function in DAX will check for 0 values on the denominator and will return a blank (which will then probably get filtered out) so you probably need to do the same thing in your report expressions.

     

    But I would suggest just referencing the same measure in your paginated report, then you only have one place to maintain the logic.