Filter and ALLEXCEPT not working

[vinaykva]

Resource Name,Month,RecordType,Actual,Flag
Person A,1/1/2018,Project,100,1
Person A,1/2/2018,Support,40,1
Person A,1/3/2018,Training,50,1
Person A,1/4/2018,Unavailable,30,1
Person A,2/1/2018,Project,80,1
Person A,2/2/2018,Support,50,1
Person A,2/3/2018,Training,20,1
Person A,2/4/2018,Unavailable,50,1
Person A,3/1/2018,Project,150,1
Person A,3/2/2018,Support,10,1
Person A,3/3/2018,Training,20,1
Person A,3/4/2018,Unavailable,20,1
Person B,1/1/2018,Project,50,0
Person B,1/1/2018,Support,100,0
Person B,1/1/2018,Training,45,0
Person B,1/1/2018,Unavailable,5,0
Person B,2/1/2018,Project,90,0
Person B,2/1/2018,Support,80,0
Person B,2/1/2018,Training,10,0
Person B,2/1/2018,Unavailable,20,0
Person B,3/1/2018,Project,180,0
Person B,3/1/2018,Support,5,0
Person B,3/1/2018,Training,10,0
Person B,3/1/2018,Unavailable,5,0

I have the below dax where I get the daily percentage based on (month and year) But along with that I want to group by Resource name and record Type
which I am not getting 

Current Filter along with this I want to group by Resource name and record Type
Total_Percentage = 
VAR TotalPerMonth =
    CALCULATE (
        SUM ( data1[Actual] ),
        FILTER ( data1, data1[Month].[Month] = EARLIER ( data1[Month].[Month] ) ),
        FILTER ( data1, data1[Month].[Year] = EARLIER ( data1[Month].[Year] ) ),
        FILTER ( data1, data1[Flag] = 1 )
    )
RETURN
    DIVIDE ( data1[actual], TotalPerMonth, 0 )

But I implemented below which is not working for me 

Total_Percentage = 
VAR TotalPerMonthRecordTypeResourceName =
CALCULATE (
SUM ( data1[Actual] ),
FILTER ( data1, data1[Month].[Month] = EARLIER ( data1[Month].[Month] ) ),
FILTER ( data1, data1[Month].[Year] = EARLIER ( data1[Month].[Year] ) ),
ALLEXCEPT(data1,data[RecordType],data1[Resource Name]),
FILTER ( data1, data1[Flag] = 1 )
)
RETURN
DIVIDE ( data1[actual], TotalPerMonthRecordTypeResourceName , 0 )

[v-piga-msft]

Hi @vinaykva ,

Have you solved the problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry

[v-piga-msft · Accepted solution]

Hi @vinaykva ,

It seems that you want to create the calculated colum. Please try the formula below.

You could use only one filter function that will give you the better performance.

Total_Percentage_ = 
VAR TotalPerMonthRecordTypeResourceName =
    CALCULATE (
        SUM ( data1[Actual] ),
        FILTER (
            'data1',
            'data1'[Record Type] = EARLIER ( 'data1'[Record Type] )
                && 'data1'[Recource Name] = EARLIER ( 'data1'[Recource Name] )
                && data1[Month].[Month] = EARLIER ( data1[Month].[Month] )
                && data1[Month].[Year] = EARLIER ( data1[Month].[Year] )
                && data1[Flag] = 1
        )
    )
RETURN
    DIVIDE ( 'data1'[Actual], TotalPerMonthRecordTypeResourceName )

Your data sample may not look obvious, I add some rows to be obivously.

Best Regards,

Cherry