Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Infinity values in calculated column without any division with zero - How to correct?

Hi! I am trying to create a calculated column by dividing 2 values as below:

 

Market value of stock/portfolio value (a calculated field)

 

I am getting infinity values for above even though portfolio value is a single decimal number. If I replace portfolio value with a fixed number (e.g. 1400000), the calculation works and there are no infinity values. 

 

Please help me in getting rid of the infinity without hard coding the portfolio value.

 

  • v-cazheng-msft's avatar
    v-cazheng-msft
    4 years ago

    Hi Anonymous ,

     

    That’s weird. Even with the sample data from you, I can’t repro this issue as well.

    Portfolio value Table

     

    Stocks Table

     

    No relationship between these two tables

     

    Calculations:

    1 Portfolio value (Measure)

     

    Portfolio value = CALCULATE(SUM('Portfolio value'[Value]),FILTER('Portfolio value', 'Portfolio value'[Holding type] <> "Liabilities"))

     

     

    2 Stock Yield (Calculated column)

     

    Stock Yield = DIVIDE(Stocks[Market Value],[Portfolio value])*Stocks[Gross indicative div yield]

     

     

    Drag Stock Yield to clustered column chart, it looks like this.

     

    Could you please drag and drop "Portfolio value" Measure to a table visual to check whether it has correctly calculated the result? In addition, please check if this issue is caused by improper data type or filters in the report. Thanks in advance!

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

     

    Best Regards,

    Community Support Team _ Caiyun

14 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous 
    you can use the DIVIDE function to remove the infinity values. You can put a default value in case of infinity such as "-" or "NA"
    syntax would be 

    Result = Divide(Market value of stock,portfolio value,"NA")

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    It would be helpful if you can tell how you calculated the portfolio value. It can be the case where the measure is non-zero without any filter context but within the row context of the table, the value of the calculated measure is becoming zero.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago
      Portfolio Value = CALCULATE(SUM('Asset Allocation (Static)'[Value]),'Asset Allocation (Static)'[Holding Type]<>"Liabilities")
       
      here, Asset Allocation (Static) is the table name
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you for your response! 

     

    Unfortunately, I have already tried using Divide and it just makes all values "-" or "NA" in case I put these as alternative output.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    the problem seems to be with the portfolio value calculated field because if I replace portfolio value with some other calculated field, it works. However, I have thoroughly check portfolio value for any errors or 0s and there's none.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Is the Portfolio value a calculated measure or a calculated column?

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        it is a measure

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    division with calculated fielddivision with fixed number

  • v-cazheng-msft's avatar
    v-cazheng-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

    May I know whether your issue has been resolved? If you still have problem on it, could you please provide some sample data from your tables and tell me the relationships between them so I can help you find the solution?

    Based on the info from you, I create some sample data to do the same calculation as you. But I don’t get the problem. So I guess it maybe something related to the data in the fields or the relationships.

     

     

     

     

    Best Regards,

    Community Support Team _ Caiyun

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you for your response. 

    Portfolio value table
    Holding typeValue
    Cash37234
    Cash8249
    Cash98436
    Fixed income109175
    Fixed income139776
    Fixed income170377
    Fixed income200978
    Alternatives9482
    Alternatives262180
    Alternatives292781
    Equity323382
    Equity353983
    Equity49244
    Liabilities58275
    Liabilities94750
    Liabilities131225
    Liabilities167700

     

    Portfolio value (calculated field) = sum of all values, condition (holding type <> Liabilities)

     

    Stocks Table
    Stock Market ValueGross indicative div yield
    ISRG1002.56
    PANW403.9
    SHOP5012.7
    UPST2010
    PYPL7014.97
    AAPL14018.02

     

    Stock Yield (Calculated column) = Market value / Portfolio value * Gross indicative dividend yield

     

    Problem: Stock yield has infinity values even though there is no division by 0 (Portfolio value is a decimal number). If I manually input a fixed number, the caculation works

    • v-cazheng-msft's avatar
      v-cazheng-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi Anonymous ,

       

      That’s weird. Even with the sample data from you, I can’t repro this issue as well.

      Portfolio value Table

       

      Stocks Table

       

      No relationship between these two tables

       

      Calculations:

      1 Portfolio value (Measure)

       

      Portfolio value = CALCULATE(SUM('Portfolio value'[Value]),FILTER('Portfolio value', 'Portfolio value'[Holding type] <> "Liabilities"))

       

       

      2 Stock Yield (Calculated column)

       

      Stock Yield = DIVIDE(Stocks[Market Value],[Portfolio value])*Stocks[Gross indicative div yield]

       

       

      Drag Stock Yield to clustered column chart, it looks like this.

       

      Could you please drag and drop "Portfolio value" Measure to a table visual to check whether it has correctly calculated the result? In addition, please check if this issue is caused by improper data type or filters in the report. Thanks in advance!

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

       

      Best Regards,

      Community Support Team _ Caiyun

  • v-cazheng-msft's avatar
    v-cazheng-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

    May I know whether your issue has been resolved? If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. Thank you very much!

     

    Best Regards,

    Community Support Team _ Caiyun