Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mattramirez2020
Helper II
Helper II

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 North1,000,000200,000800,000720,000
Dallas South2,000,000300,0001,700,0001,540,000
TOTAL3,000,000740,0002,260,0002,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!!!! 

 

 

1 ACCEPTED 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)

View solution in original post

4 REPLIES 4
lbendlin
Super User
Super User

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))

 

 

lbendlin_0-1596940828403.png

 

 

 

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?

 

081020.PNG

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.