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,

 

Please share some sample data to help us research on these formulas.


Based on your screenshots, I found it's a request which similar as 'calculate the original value and display the it at specific group level'.(similar as you post previous)

 

So I recommend you to take a look below methods to deal with these similar requirement:
1. Use conditionals to control the measure works on different group level.

Total Measure(Row Count) = 
IF(ISBLANK(MAX('Table'[Date]))=FALSE(),
if(COUNT('Table'[Date])=COUNTX(ALL('Table'),[Date]),
 "All", 
 if(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[Date].[Year]=MAX([Date].[Year])),[Date]),
	 "Year Level Total",
	 if(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[Date].[Year]=MAX([Date].[Year])&&[Date].[QuarterNo]=MAX([Date].[QuarterNo])),[Date]),
		 "Quarter Level Total",
		 	 if(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[Date].[Year]=MAX([Date].[Year])&&[Date].[QuarterNo]=MAX([Date].[QuarterNo])&&[Date].[MonthNo]=MAX([Date].[MonthNo])),[Date]),
		 	"Month Level Total",
			 "Day Level Total")
			 ))))


2. Get current item and use it to find out the child item and calculate on these value.

You can try to use math function to get the current value(date,number) or use lastnonblank function to get the text value(this function not works on column who contains blank records).

 

Regards,

Xiaoxin Sheg

 

  • 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