Calculate sum(EAC) row by row of 3 columns based on condition

[Anonymous]

Hi ,

I am new to Power BI.

I want to calculate EAC of all rows, for whole column.

For example:

	Forecast	Actual	Forecast	Actual	Forecast	Actual
	Jan	Jan	Feb	Feb	Mar	Mar	EAC
1	$10	$15	$67	$33	$78	0	$126
2	$15	$23	$34	$24	$34		$81
3	$15	$10	$54	$54	$21	0	$85
4	$23	$22	$32	$21	$76		$119
5	$20	$45	$87	$23	$56		$124
6	$54	$35	$65	$87	$72	0	$194

In the above column I want to calculate EAC, but the logic here is, we have got actuals till Feb, so the EAC will be-

Jan Actuals+Feb Actuals +Mar Forecast.

Similarly, if Feb Actuals would have been zero, the Formula would be :- Jan Actuals + Feb Forecast +March Forecast.

I need a formula to check first if Feb or march contains all rows as zero or null in the whole, and then calculate based on that.

Or if we can have a user input to select the month till which we have got actuals, so that EAC will be calculated on that basis.

Please help.

[Anonymous]

Hi,

I had dumped the values from your sample and created the table(table1) and created a measure with the below formula to calculate the EAC,

EAC = SWITCH(true(),
            IF(ISBLANK(Sum(Table1[Actual Jan])),0,Sum(Table1[Actual Jan]))=0,sum(Table1[Forecast Jan])+SUM(Table1[Forecast Feb])+Sum(Table1[Forecast Mar]),
            IF(ISBLANK(Sum(Table1[Actual Feb])),0,Sum(Table1[Actual Feb]))=0,sum(Table1[Actual Jan])+SUM(Table1[Forecast Feb])+Sum(Table1[Forecast Mar]),
            IF(ISBLANK(Sum(Table1[ActualMar])),0,Sum(Table1[ActualMar]))=0,sum(Table1[Actual Jan])+SUM(Table1[Actual Feb])+Sum(Table1[Forecast Mar]),
            sum(Table1[Actual Jan])+SUM(Table1[Actual Feb])+Sum(Table1[ActualMar]))

[Anonymous · Accepted solution]

Hi, 

Thanks it worked. 🙂

[Anonymous]

Hi,

Can we create calculated columns using the above same formula from Measure.

Cause when I use the same formula, I am not getting the correct numbers.

Appreciate your help.