Hello,
A group of 5 partners have expenses that are incurred. Typically, they divide it equally, so 20% per partner. Occasionally, an expense will be allotted more towards one partner than the others. When it comes time to invoice the partners, each partner must pay their amount due.
I have a setup similar to the Slice based on User Input template.
The user selects a partnerโs name from a drop down, which populates a table called Invoice_Filter with their name. The various expenses are shown via a dashboard view, and the user can select an expense, see the total amount, and see the percentages allotted to each partner. Sample table below
I want to create a virtual column that will take the amount and divide by the appropriate percentage that has been assigned to the selected partner. Such as:
Partner: Name 1
Amount: $100
Per Partner: $40 โ Virtual Column Desired
Partner: Name 3
Amount: $100
Per Partner: $20 โ Virtual Column Desired
(Above Formula would be something like: [Amount] * (Partnerโs name [Percentage])
I thought FILTER would do the job, but I canโt seem to figure it out. Iโm sure itโs something very simple but I canโt wrap my mind around it.
Any help would be greatly appreciated. Thank-you.
Solved! Go to Solution.
Assuming the columns have the same name, try:
[AMOUNT]*
SWITCH(
INDEX(Invoice_Filter[Partners], 1),
"Joe", [Joe],
"Steve", [Steve],
"John", [John],
"Dave", [Dave],
"Brian", [Brian],
0
)
Hi @steve1123
I think it would be interesting to know more about the table structure for percentage alloting, and about more table structures in your app, in general.
However, based on your screenshot, and assuming:
Then here is my suggestion:
[Amount]*ANY(Percentage[Name1])
for name1
[Amount]*ANY(Percentage[Name2])
for name2
etc.
Thank-you for your reply.
The table that contains the filter that the user uses looks like the image below and is called Invoice_Filter.
And yes, the table referenced in the original post does start from row one. My apologies, I just copied and pasted for the screenshot and didnโt put it in row 1.
I think the main issue is that the Invoice_Filter does not have the percentage of the transaction, that is only contained in the Invoice_SA sheet that is reference in the screenshot of the original post.
So, I need a way of using the name value in column B of Invoice_Filter called โPartnersโ (screenshot in my original post), to then search Invoice_SA (screenshot attached to this post) for the same name along the top row (columns C to G) and pull the related percentage for the charge. And thatโs what would get multiplied by the โAmountโ.
Iโm sorry, I know Iโm not doing a great job of explaining this. Please feel free to ask further clarifying questions.
Thank-you.
For 1) You need a user table
For 2) The following AppFormula can do the trick:
[_THISROW].[AMOUNT]*[_THISROW].[NAME X]
Now, make sure the columns that has percentages are configured as โPercentโ and donโt try to use your data outside of AppSheet if itโs not just for Data Analysis (Using PowerBI for example)
I need to search the Invoice_SA table for the name in my user table, Invoice_Filter, and retrieve related percentage for that transaction.
Forgive me, but the only issue I am having with your suggested expression is that the name is not fixed. It needs to pull that name based on the user table, Invoice_Filter, under column B โPartnersโ. Unless Iโm missing something (I likely am, no doubt).
Thank-you for your time.
Ok, I ignored this a bit in my previous answer. So you donโt have a user table that filters based on the user that is using the app, instead is a manual filter.
You want 1 VC that calculates the Amount for the Partner that is selected on the Invoice_Filter table?
Yes and Yes! Absolutely! Both correct.
The user manually selects the Name. This updates the Invoice_Filter table (which only has one row with the selected Partnersโ name, just like the โSlice based on user inputโ template). Then I want one VC (in the current table, Invoice_SA) that will find the selected Partnersโ name (from the Invoice_Filter) table, within columns C to G of the Invoice_SA table, and retrieve the appropriate percentage for the Amount.
OK.
The problem is the fact that you are using Columns to hold values for people is maybe not the best Database design.
Anyway, please provide the actual name of partners to help with the expression
Joe, Steve, John, Dave, Brian
Thanks!
Assuming the columns have the same name, try:
[AMOUNT]*
SWITCH(
INDEX(Invoice_Filter[Partners], 1),
"Joe", [Joe],
"Steve", [Steve],
"John", [John],
"Dave", [Dave],
"Brian", [Brian],
0
)
Yes!! That works beautifully. Thank-you both very much for your time and assistance with this.
User | Count |
---|---|
25 | |
25 | |
24 | |
21 | |
20 |