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 and 70% of total for handlers divided by total handlers.

 

The new request is that I have a second view of this table totalizing this allocation per names in order to know total amount calculated, as follows:

 

 

note my total is a metric (CrossLiquido). Any further explanatios, please let me know.

 

Thanks in advance

 

 

 

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

9 Replies

  • 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