Forum Discussion
Splitting Revenue
I have this table:
I need to write a measure that calculates what each person made when they owned the account. So basically, the [invoice date] (not pictured) would need to fall in between each persons [entry date] and [exit date].
I attempted to write a measure doing so but it didn't work correctly. Was dividing everybody's total by 3 for some reason:
Anonymous I've tried with below sample Invoice data....
Then try with below expression as a "New Column" in your AMs table
Revenue = CALCULATE(SUM(Test66Invoices[Revenue]),FILTER(Test66Invoices,Test66Invoices[InvoiceDate]>=Test66Lkp[EntryDate] && Test66Invoices[InvoiceDate]<Test66Lkp[ExitDate] && Test66Lkp[Code] = Test66Invoices[Code]))
Added another condition to check the Code as well if incase if you have multiple codes as well.
9 Replies
- Anonymous Sample data will be helpful to create revenue measure. Here is my identifications about the calculation that you are using - As the entry and exit date overlap for one day (Example - Kathy exit date is same as Sara Entry date) so you need to change the date comparison logic a bit either >entrydate and <=exitdate or >=entry date and <exitdate. Then, give a try by having ALL(TableName) as first parameter in the FILTER function.
PattemManohar sure thing. So a little more background. I had 1 table that contains all companies and their revenue over the years:
Another table in the same database that contains nothing but the companies and the AM's that have managed them over the years. I merged the 2 tables based on the [Co] field so it results in this, before any written formulas:
Then in my attempt to split their revenue appropriately I wrote this formula:
Which then gave me these results:
Which seems like it's working correctly, but for whatever reason it's undercutting everybody's value. I've discovered that if I multiply everybody's value by 3, THEN I get the values I'm looking for. My guess is, it's dividing everybody's amount by 3 because the original [Extended] column is splitting the revenue 3 ways since there's been 3 AM's, but I don't know how to get around that. I tried creating an additional measure to count the amount of AM's per company and multiplying by that number, but it didn't come out correctly.
- Anonymous Do you want to split the Total Revenue among the AM's who managed. (OR) You want to calculate the Total Revenue that was generated for their respective period as AM. Please try to post the sample data in copiable format to suggest any solution.