Grand total with IF Function

[Anonymous]

I have two tables I'm using in a formula - one is an Actuals table and one is a forecast table. My formula basically says if Actuals are larger than forecast, use Actuals if not use the forecast value. This works for all the individual rows when I look at specific programs but the grand total row is not the sum of all the individual programs. I tried adding a SUMX but I must be doing something wrong. Here's my formula:
CQ Factored ACT = SUMX( 'ACT DATA' , ( IF ( 'ACT DATA' [value] > 'FCST DATA' [value], 'ACT DATA' [value] , 'FCST DATA [value]))

So, what I want to see in my grand total is the $1,800 (the total of all the individual programs) but what I get is the $1,750 since the total of FCST is larger than the total of ACT.

	ACT	FCST	Factored
Program 1	50	100	100
Program 2	900	850	900
Program 3	650	800	800
	1,600	1,750	1,800

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Aree]

If both your forcast and actual are linked then at the linked table you can create a calculated column that uses an IF to assinged a value to that column using your comparison logic

Then a sum of that will give you a sum of that result and since this is assinged per project then you can slice that by program.

However i am assuming that there is a 1:1 relationship between both ProjectID and Actual and ProjectID and Forecast. 

[MFelix]

Hi @Aree,

Although your solution works calculated columns add space to your file and low flexibility since they are made at row level. As a best practice a measure is always preferible to a column. If you can make the calculations with a measure you should not use.columns.


Regards
MFelix

[Aree]

@MFelix

I agree that it does contribute in some manner to the overall size of your file. Given the number of rows this contribution can be insignificant to immense. 

Cheers Mate ! 

[MFelix]

Hi @Anonymous,

I'm assuming your two tables are related by the Program column and that this as a relation of one to one. Create the following measure:

Total_Projects =
SUMX (
    ADDCOLUMNS ( Actuals; "FCT"; RELATED ( FRCST[FCST ] ) );
    IF ( [ACT] > [FCT]; [ACT]; [FCT] )
)

The SUMX depends on a table to return the result of the sum since you have values on two tables actuals and forecast the result is not as expected.

Regards,

MFelix

[Anonymous]

Sorry, I was trying to simplify my explanation by saying only two tables. The ACT and FCST tables both have many rows for each project. There is a third table that they are both linked to that has the unique project ID. They are all connected by the Project ID. 

[MFelix · Accepted solution]

Hi @Anonymous

Then just change the measure to:

Total_Projects =
SUMX (
    ADDCOLUMNS ( Project;"ACT"; RELATED(ACT[ACT]; "FCT"; RELATED ( FRCST[FCST ] ) );
    IF ( [ACT] > [FCT]; [ACT]; [FCT] )
)

Should work in the same way but you have the connection table as a starting point.

Regards,

MFelix