Forum Discussion

Fcoatis's avatar
Fcoatis
Icon for Post Patron rankPost Patron
9 years ago
Solved

Need help with this model v2

Hello again,   This is a new version of my previous post. I have this table:    I have to allocate total amount of each job following the rule 30% of total for doers divided by total doers ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi Fcoatis,

     

    Sorry, it seems like I miss the the type column and only calculate the distinct value.(so the total amount not match the original amount)

     

    Modified formula:

    Table = SUMMARIZE(Allocation,[Job],[Name],[Group],[Type],
    "Date",LOOKUPVALUE(fMovement[Date],[Job],[Job]),
    "Actual Amount",LOOKUPVALUE(fMovement[Amount],[Job],[Job])*SWITCH([Group],"Doer",.3,"Handler",.7)/
    COUNTX(FILTER(ALLEXCEPT(Allocation,Allocation[Name]),[Job]=EARLIER([Job])&&[Group]=EARLIER([Group])),[Group]))

     

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi Fcoatis,

 

For your scenario, I think summary table will be suitable for your requirement.

I create a summary table to store these analysed actual user amount, you can use it to create the matrix visual.

Table = SUMMARIZE(Allocation,[Job],[Name],[Group],
    "Date",LOOKUPVALUE(fMovement[Date],[Job],[Job]),
    "Actual Amount",LOOKUPVALUE(fMovement[Amount],[Job],[Job])*SWITCH([Group],"Doer",.3,"Handler",.7)/
    COUNTX(FILTER(ALLEXCEPT(Allocation,Allocation[Name]),[Job]=EARLIER([Job])&&[Group]=EARLIER([Group])),[Group]))

 

Comment:

LOOKUPVALUE(fMovement[Date],[Job],[Job]) , -look up the job date.

LOOKUPVALUE(fMovement[Amount],[Job],[Job]) - job amount

SWITCH([Group],"Doer",.3,"Handler",.7) - percent of group

COUNTX(FILTER(ALLEXCEPT(Allocation,Allocation[Name]),[Job]=EARLIER([Job])&&[Group]=EARLIER([Group])),[Group]) - current group count , used to calculate actual amount of group member.(divide)

 

 

 

Matrix:

 

 

 

Regards,

Xiaoxin Sheng

Fcoatis's avatar
Fcoatis
Icon for Post Patron rankPost Patron
9 years ago

AnonymousThank you so much for your time,

 

But something is missing:

Project 2 Total is 120.000

Project 5 Total is 10.000

Project 7 Total is 35.000

 

If you could check summarize. I´ll be extremely grateful.

Thank you once more

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Fcoatis,

     

    Sorry, it seems like I miss the the type column and only calculate the distinct value.(so the total amount not match the original amount)

     

    Modified formula:

    Table = SUMMARIZE(Allocation,[Job],[Name],[Group],[Type],
    "Date",LOOKUPVALUE(fMovement[Date],[Job],[Job]),
    "Actual Amount",LOOKUPVALUE(fMovement[Amount],[Job],[Job])*SWITCH([Group],"Doer",.3,"Handler",.7)/
    COUNTX(FILTER(ALLEXCEPT(Allocation,Allocation[Name]),[Job]=EARLIER([Job])&&[Group]=EARLIER([Group])),[Group]))

     

     

    Regards,

    Xiaoxin Sheng

  • Fcoatis's avatar
    Fcoatis
    Icon for Post Patron rankPost Patron
    9 years ago

    AnonymousThank you very much. Not only worked but also learned a lot.

     

    My best regards.