Need Help with Conditional Allocation Formula

[jl20]

Hi,

This is a follow-up to a question I posted yesterday. I'm trying to figure out how to achieve the following outcome. I want a calculated column or measure to recalculate the estimated revenue column based on what's in the revenue to date column (to avoid double counting). Ultimately I want to be able to slice on workgroup and sum up the adjusted total of the estimated revenue column. Any ideas? Thanks in advance!

[Anonymous]

Hi @jl20,

You can try to use below formula if it suitable for your requirement.

Sample measure:

Dynamic Revenue=
var currProj=Max([Project #])
var currRevenue= Max(Table[Revenue to Date])
var totalEsti= SUMX(FILTER(ALL(Table),[Project #]= currProj),[Estimated Revenue])
var totalRevenue= SUMX(FILTER(ALL(Table),[Project #]=currProj),[Revenue to Date])
var groupCount=Calculate(DistinctCount(Table[Workgroup]),Table[Project #]= currProj)
return
 IF(groupCount>1,
 if(currRevenue<>0, currRevenue/totalRevenue *totalEsti,AVERAGEX(FILTER(ALL(Table),[Project #]= currProj),[Revenue to Date])),Max(Estimated Revenue]))

If above is not help, please share some sample data to test.

Regards,
Xiaoxin Sheng

[jl20]

That seemed to give me some weird results that didn't make much sense. How can I attach an excel file with sample data?

[Anonymous]

Hi @jl20,

You can upload to 1dv and share us the link.

Regards,

Xiaoxin Sheng

[jl20]

It looks like I am restricted from sharing on 1dv outside my organization. Is there another service I can use to share?

EDIT: Try this link: 

https://srr-my.sharepoint.com/personal/jleins_srr_com/_layouts/15/guestaccess.aspx?guestaccesstoken=...

[Anonymous]

Hi @jl20,

I can't reach to your link, I think you can also try to use google drive.

Share a document using SharePoint or OneDrive

Regards,

Xiaoxin Sheng

[jl20]

Here's a link that should work:

Sample Data Set

[Anonymous · Accepted solution]

Hi @jl20,

I modified the formula based on your sample data, you can try it if suitable for your requirement.

Dynamic Revenue = 
var currProj=Max([MatterId])
var currRevenue= Max('Sample Data'[FeesBilled])
var totalEsti= SUMX(FILTER(ALL('Sample Data'),[MatterId]= currProj),[EstimatedFees])
var totalRevenue= SUMX(FILTER(ALL('Sample Data'),[MatterId]= currProj),[FeesBilled])
var groupCount=Calculate(DistinctCount('Sample Data'[WorkgroupName]),ALL('Sample Data'),'Sample Data'[MatterId]= currProj)
return
 IF(groupCount>1,
 if(currRevenue<>0, currRevenue/totalRevenue *totalEsti, Data'),[MatterId]= currProjAVERAGEX(FILTER(ALL('Sample ),[EstimatedFees])),Max([EstimatedFees]))

Regards,

Xiaoxin Sheng