Forum Discussion
Corporate Reallocation
Hello, I have a few profit measures I would like to now include a markup equal to the percentage of an offices' payments divided by the company payments multiplied by the corporate expenses.
Example:
Net Profit w/Corp Contribution=Office Profit Measure +(Office Payments/Company Payments)*(Corporate Profit)
| Clinic_Name (column in data) | Net Payments (Measure) | Expenses (Measure) | Net Profit (Measure) | Net Profit w/Corp Contribution (what I want to create!) |
| Corporate | - | 240,000 | -240,000 | - |
| Dallas North | 1,000,000 | 200,000 | 800,000 | 720,000 |
| Dallas South | 2,000,000 | 300,000 | 1,700,000 | 1,540,000 |
| TOTAL | 3,000,000 | 740,000 | 2,260,000 | 2,260,000 |
So I am trying to make the last column and I have a column labeled clinic_name that I am using for the rows presently and then established measures for the other. How can I create this last measure that takes anything allocated to Clinic_Name="Corporate" and then add it all the other clinics (not="Corporate") as a percentage of their office payments ((1,000,000/3,000,000)*240,000)=80,000) that would be able to create the last column?
Any help would be appreciated!!!!
Here's a version that handles the totals "correctly" .
NP = var thisnp=sumx(Departments,Departments[Net Payments]) var allnp=sumx(all(Departments),Departments[Net Payments]) var corpexp=sumx(filter(all(Departments),Departments[Clinic_Name]="Corporate"),Departments[Expenses]) var ind=sumx(filter(Departments,Departments[Clinic_Name]<>"Corporate"),Departments[Net Payments]-Departments[Expenses]-corpexp*divide(thisnp,allnp)) var tot=sumx(Departments,Departments[Net Profit]) return if(HASONEVALUE(Departments[Clinic_Name]),ind,tot)
4 Replies
This measure will get you halfway there. The result for Corporate is up for discussion - but it also impacts the totals, and it does so in a very non-trivial way.
NP = var thisnp=sumx(Departments,Departments[Net Payments]) var allnp=sumx(all(Departments),Departments[Net Payments]) var corpexp=sumx(filter(all(Departments),Departments[Clinic_Name]="Corporate"),Departments[Expenses]) return sumx(filter(Departments,Departments[Clinic_Name]<>"Corporate"),Departments[Net Payments]-Departments[Expenses]-corpexp*divide(thisnp,allnp))Here's a version that handles the totals "correctly" .
NP = var thisnp=sumx(Departments,Departments[Net Payments]) var allnp=sumx(all(Departments),Departments[Net Payments]) var corpexp=sumx(filter(all(Departments),Departments[Clinic_Name]="Corporate"),Departments[Expenses]) var ind=sumx(filter(Departments,Departments[Clinic_Name]<>"Corporate"),Departments[Net Payments]-Departments[Expenses]-corpexp*divide(thisnp,allnp)) var tot=sumx(Departments,Departments[Net Profit]) return if(HASONEVALUE(Departments[Clinic_Name]),ind,tot)lbendlin Thank you so much! I was able to adapt this to work with my measures. I hit one snag but may just be making this harder than it has to be.
I am still relatively new but made it work using that format but every time it returned an amount it was all years instead of the filtered years I selected on the page. To combat that, I added the filter manually (lines 3 and 4) but it is not dynamic. To make this align to the dates that are filtered (say for instance 2020, or march 2020, etc), is there a better approach?
