Forum Discussion

Fcoatis's avatar
Fcoatis
Post 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

Fcoatis's avatar
Fcoatis
Post Patron
9 years ago
Tot Allocation = 
VAR current_Amount =
    LOOKUPVALUE (fMovement[Amount];[Job]; MAX ( Allocation[Job] ) )
VAR current_Job =
    LASTNONBLANK ( Allocation[Job]; [Job] )
VAR current_Group =
    LASTNONBLANK ( Allocation[Group]; [Group] )
RETURN
    IF (
        COUNTROWS ( Allocation)
            = COUNTROWS ( FILTER ( ALL ( Allocation ); [Job] = current_Job ) );
        current_Amount;
        IF (
            current_Group = "Handler";
            ,7 * current_Amount
                / COUNTROWS (
                    FILTER (
                        ALL ( Allocation );
                        [Job] = current_Job
                            && [Group] = "Handler"
                    )
                  );
            ,3 * current_Amount
                / COUNTROWS (
                    FILTER (
                        ALL ( Allocation );
                        [Job] = current_Job
                            && [Group] = "Doer"
                    )
                  )
        )
    )

Ok I got the Allocation working. Now I have to scale up for names. I´m trying to grab by your example. Updated the model in the previous link.

 

thanks

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
    Post 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
        Post Patron
        9 years ago

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

         

        My best regards.