SumX using If Condition

[Anonymous]

Hi,

I m new to Power Bi, need calculate Overdue Total based on below formula.

Hi,

I m new to Power Bi, need calculate Overdue Total based on below formula.

Actual = IF(Actual_Start_CT > Target_Start_CT ,Actual_Start_CT - Target_Start_CT ,0)

Target= IF(Actual_Start_CT > Target_Start_CT ,Actual_Start_CT - Target_Start_CT ,0)

Total Days = Actual+Target 

Actual_Start_CT	Target_Start_CT	Actual_End_CT	Target_End_CT	Overdue Days
4	2	1	2	(4-2)+0 =2
8	3	3	1	(8-3)+(3-1)=7
Total				9

Actual_Start_CT	Target_Start_CT	Actual_End_CT	Target_End_CT	Overdue Days
4	2	1	2	(4-2)+0 =2
8	3	3	1	(8-3)+(3-1)=7
Total				9

[harshnathani · Accepted solution]

Hi @Anonymous ,

Try this measure

SUM OF CT =

var _tab =

ADDCOLUMNS(

SUMMARIZE('Table','Table'[Actual_Start_CT],'Table'[Actual_End_CT],'Table'[Target_Start_CT],'Table'[Target_End_CT]),

"Act", IF('Table'[Actual_Start_CT] > 'Table'[Target_Start_CT] ,'Table'[Actual_Start_CT] - 'Table'[Target_Start_CT],0),

"Tar", IF('Table'[Actual_End_CT] > 'Table'[Target_End_CT], 'Table'[Actual_End_CT] - 'Table'[Target_End_CT], 0)

)

RETURN

SUMX(_tab,[Act]+[Tar])

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[amitchandak]

@Anonymous , What is issue in creating you can have 3 column like this

Actual = IF([Actual_Start_CT] > [Target_Start_CT] ,[Actual_Start_CT] - [Target_Start_CT] ,0)

Target= IF([Actual_Start_CT] > [Target_Start_CT] ,[Actual_Start_CT] - [Target_Start_CT] ,0)

Total Days = [Actual]+[Target]

or

Total Days =

var  _Actual = IF([Actual_Start_CT] > [Target_Start_CT] ,[Actual_Start_CT] - [Target_Start_CT] ,0)

car _ Target= IF([Actual_Start_CT] > [Target_Start_CT] ,[Actual_Start_CT] - [Target_Start_CT] ,0)

return

 _Actual+_Target

[Anonymous]

I want to create sumx measure for same. I have total 8 columns like this that's why avoiding calculated columns .