Hi,
I hope you can help. I have tried to solve for a few hours but am stuck.
Background
I have two sheets
Sheet one if a member list (Member ID, Member details, List of Sheet Two costs
Sheet two is a list of member costs (Member ID, Cost Description, Cost Amount)
In the app I can see all the costs for each member.
where I am stuck
I want to create a virtual field that has the sum of all the costs for the member. I dont seem to be able to do it.
What I have tried
sum(
select(Costs[Amount],Cost[Member ID]=[MemberID])
)
This gives a "Cannot compare List with Text in" error.
My question
Is this possible? Can you give some examples.
I am sure it is possible as it is a standard Order and Order Item pattern too. So you can see the total value of the order items in an order.
Any help welcome
Solved! Go to Solution.
Ja!
SUM([Related Ticks][Amount])
Ohne die Einbeziehung der Referenzspalte wรคre der Ausdruck:
SUM(SELECT(Costs[Amount],[Member ID]=[_THISROW].[MemberID])
Sie sollten wirklich Referenzen verwenden, um Ihre Daten miteinander zu verbinden. Von hier aus kรถnnen Sie all diesen Brute-Force-Overhead vollstรคndig aus Ihrem System entfernen. Tatsรคchlich ist diese Referenzverbindung in den meisten Fรคllen bereits vorhanden... Sie nutzen es einfach nicht optimal.
z.B. SUMME(SELECT(Kosten[Betrag], [Mitgliedsnummer] = [_THISROW]. [Mitgliedsnummer]))
wรผrde werden
Die Auswirkungen dieser beiden Formeln auf Ihr System sind ENORM ... wรคhrend sie das gleiche Ergebnis liefern.
Thank you so much for taking the time to respond.
The challenge with using Sum() is it gives me the sum of ALL the items. I only want the sum of the related amounts. Is this the [Related ticks] ?
Ja!
SUM([Related Ticks][Amount])
Ohne die Einbeziehung der Referenzspalte wรคre der Ausdruck:
SUM(SELECT(Costs[Amount],[Member ID]=[_THISROW].[MemberID])
You are amazing... Thank you!!!
Hi, try
sum(
select(Costs[Amount],[Member ID]=[_thisrow].[MemberID])
)
Perfect!! Thank you... It is working
User | Count |
---|---|
37 | |
30 | |
29 | |
20 | |
18 |