Issue with Virtual column

[Boa_Casa]

Hi,

I have created an app for expense management. Where in I have two tables as below :

1. Income table : In this income for a given month across people is maintained.

2. Bank statement table : In this table expenses across a month are displayed along with contribution of each person against the expense based on income from income table

In income table I have an virtual column in which I want to track saving against each person based on sum of contribution from the bank statement table.

The issue I am facing is that the virtual column in income table which shows the sum of contribution against a person based on expenses from the bank statement table is not showing any value where seen in table format but when I edit the row in form I am able to see the correct values. Attaching the screenshot for better understanding.

If someone can help me understand on why this is happening?

[Go]

Please post the formula for each virtual column.

[Boa_Casa]

Hi,

The formula for the virtual column with issue is : 

IF([Person]="Person 1",SUM(SELECT(DBS Account[Person 1 Contribution],[Month / Year]=[_THISROW].[Month / Year])),
SUM(SELECT(DBS Account[Person 2 Contribution],[Month / Year]=[_THISROW].[Month / Year])))

[Go]

Can you post a snapshot of DBS Account table ?

[Boa_Casa]

Sure the virtual column has following formulas :

Person 1 Contribution : [Debit Amount]/
ANY(SELECT(Income[Person Contribution ratio],[Person]="Person 1"))

Person 1 contribution : [Debit Amount]/
ANY(SELECT(Income[Person Contribution ratio],[Person]="Person 2"))

Snapshot of table :

[Go]

Circular references to dynamic virtual columns are not recommended as they degrade performance as the number of data items increases.

Try changing the Contribution ratio to either a fixed value or a static reference to another table.

Another possibility.
The part that seems to be the cause is that the Data type column (Month/Year) is included in the extraction condition of the SELECT function.
Try splitting this into two TEXT type columns: month and year.

IF([Person]="Person 1",SUM(SELECT(DBS Account[Person 1 Contribution],AND([Month]=[_THISROW].[Month],[Year]=[_THISROW].[Year]))),
SUM(SELECT(DBS Account[Person 2 Contribution],AND([Month]=[_THISROW].[Month],[Year]=[_THISROW].[Year]))))