Forum Discussion

Fcoatis's avatar
Fcoatis
Post Patron
9 years ago
Solved

Need help with this model

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

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      Fcoatis
      Post Patron
      9 years ago

      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

    • Fcoatis's avatar
      Fcoatis
      Post Patron
      9 years ago

      Hello Anonymous,

       

      Can you help me one more time with this model?

       

      Thanks in advance

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi Fcoatis,

         

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

         

        Regards,

        Xiaoxin Sheng