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,

 

It seems like you share the previous data table, I can't find new columns 'Nome','JOB' in sample file.

 

Regards,

Xiaoxin Sheng

    • Fcoatis's avatar
      Fcoatis
      Icon for Post Patron rankPost 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