Forum Discussion
Solved
Filter and ALLEXCEPT not working
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,...
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
v-piga-msft
Resident Rockstar
Resident RockstarHi 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