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?
Please post the formula for each virtual column.
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])))
Can you post a snapshot of DBS Account table ?
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 :
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]))))
User | Count |
---|---|
38 | |
34 | |
27 | |
23 | |
17 |