Need help with this model v2

[Fcoatis]

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

[v-shex-msft]

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

[Fcoatis]

Thank you once again @v-shex-msft,

Here is a sample data detailing what I need.

https://www.dropbox.com/sh/ziioxdnfts93ahy/AAAc3x_D3BLAiIGixpwOg-lOa?dl=0

Please let me know if you need further details.

Best Regards

[v-shex-msft]

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]

Thats right @v-shex-msft, I translated them to english. 

Can you see the model in excel? 

Regards.

This link is the model in pbix.

https://www.dropbox.com/s/d3g0m8g2g4uz7c9/Allocation.pbix?dl=0

[Fcoatis]

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

[v-shex-msft]

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]

@v-shex-msftThank 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

[v-shex-msft · Accepted solution]

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]

@v-shex-msftThank you very much. Not only worked but also learned a lot.

My best regards.