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

[Anonymous]

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]

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

[Anonymous]

Thank you for your response. 

Portfolio value table
Holding type	Value
Cash	37234
Cash	8249
Cash	98436
Fixed income	109175
Fixed income	139776
Fixed income	170377
Fixed income	200978
Alternatives	9482
Alternatives	262180
Alternatives	292781
Equity	323382
Equity	353983
Equity	49244
Liabilities	58275
Liabilities	94750
Liabilities	131225
Liabilities	167700

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

Stocks Table
Stock	Market Value	Gross indicative div yield
ISRG	100	2.56
PANW	40	3.9
SHOP	50	12.7
UPST	20	10
PYPL	70	14.97
AAPL	140	18.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 · Accepted solution]

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]

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]

division with calculated fielddivision with fixed number

[Anonymous]

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]

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

[Anonymous]

it is a measure

[Anonymous]

Try creating a calculated column with just this measure. 
Like
Test Portfolio Value = [Portfolio Value]
and see what results you are getting at each row

[Anonymous]

tried this as well. It treats the portfolio value column the same way as the measure. Basically, no difference in output

[Anonymous]

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]

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]

Portfolio Value = CALCULATE(SUM('Asset Allocation (Static)'[Value]),'Asset Allocation (Static)'[Holding Type]<>"Liabilities")

here, Asset Allocation (Static) is the table name

[Anonymous]

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")