Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!!!!
Solved! Go to Solution.
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)
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?
You can either let the filter context do the work for you, or you can use a variable that picks the current year from the filter context.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |