Need help with this model

[Fcoatis]

Hello All,

I need help to create a mesure to allocate total amount of projects following specific rules as I´ll explain below:

Table 1 is a fictitious table of projects (named jobs)

Table 1

Table 2 (below) is the original allocation table that I managed to pivot in PowerQuery as Table 3

Table 2

After PowerQuery:

Table 3

For every job I have to allocate 30% of total for "Doers" and 70% of total for "Handlers". Inside every job I may have 1 to 5 "Doers"

and 1 to 5 "Handlers". Thus I need a final Table as follows:

Table 4 (Pivot Table)

Notes: There is a consultant named Mary and one Named Mari (this is on purpose). I may need to agregate by name so in job 6 John will total 25,000 alone. In job 7 Mary will total 10,500 as "Doer" and Paul 24,500 as "Handler". Is it possible to have a Measure for this model?

Here is the link of the sample.

 Dropbox

Thank you in advance

Fcoatis

[Anonymous · Accepted solution]

Hi @Fcoatis,

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

Calculate Amount = 
var current_Amount =LOOKUPVALUE(Jobs[Amount],[Job],MAX(AllocationPorJobs[Job]))
var current_Job=LASTNONBLANK(AllocationPorJobs[Job],[Job])
var current_Group=LASTNONBLANK(AllocationPorJobs[Grupo],[Grupo])
return
IF(COUNTROWS(AllocationPorJobs)=COUNTROWS(FILTER(ALL(AllocationPorJobs),[Job]=current_Job)),current_Amount,
	IF(current_Group="Handler",.7*current_Amount,.3*current_Amount/COUNTROWS(FILTER(ALL(AllocationPorJobs),[Job]=current_Job&&[Grupo]<>"Handler"))))

Regards,

Xiaoxin Sheng

[Fcoatis]

Hello @Anonymous,

Can you help me one more time with this model?

Thanks in advance

[Anonymous]

Hi @Fcoatis,

Since current thread has closed, I'd like to suggest you open a new topic for your issues.

Regards,

Xiaoxin Sheng

[Fcoatis]

Hello @Anonymous , 

Have you had a chance to look the new thread?

Best Regards 

[Fcoatis]

Thank you very much Xiaoxin. I understood the idea and iniatilly it is suitable. Allthough I´ll have to figure out pivoting fields like dates and names.

Thank you again for your time spent trying to help me.

Regards

Fcoatis