Comparison using AddColumns

[Anonymous]

I am using the formula below to determine which is greater (the EOC ACT or the SRO FCST). It then adds the Greater Value to the CM Factored ACT column. As you can see this particular project is not working for the 3RD Party category. But the formula does work properly for other projects (ones that have a value in the SRO FCST column). My guess is that since SRO FCST for 3rd Party does not exist for this project, the formula has nothing to compare EOC ACT to and therefore leaves it blank. What can I add to the formula to say if there is nothing to compare to just use EOC ACT? I tried adding an IFERROR around the existing IF but no change. 

Greater Value = SUMX(Addcolumns('SRO DATA',"ACT",'EOC DATA'[EOC NonLabor],"FCST",'SRO DATA'[SRO NonLabor]),IF([ACT]>[FCST],[ACT],[FCST]))

[v-lili6-msft · Accepted solution]

HI, @Anonymous

First, reason for the wrong result is My guess is that since SRO FCST for 3rd Party does not exist for this project, the formula has nothing to compare EOC ACT to and therefore leaves it blank.

Second,  What can I add to the formula to say if there is nothing to compare to just use EOC ACT?

You need to add a fact EOC_Type table then using addcolumns function 

Greater Value = SUMX(Addcolumns('EOC_Type',"ACT",'EOC DATA'[EOC NonLabor],"FCST",'SRO DATA'[SRO NonLabor]),IF([ACT]>[FCST],[ACT],[FCST]))

Thrid, you could just use this simple formula

Greater Value =
IF (
    'EOC DATA'[EOC NonLabor] > 'SRO DATA'[SRO NonLabor],
    'EOC DATA'[EOC NonLabor],
    'SRO DATA'[SRO NonLabor]
)

Best Regards,

Lin

[Anonymous]

 Hi v-lili6-msft

I did try using the simpler formula you mentioned but the problem with it is that the totals are not the sum of all the lines - the totals do the same formula at a total level; therefore, the total always equals SRO FCST. 

Using your other suggestion of adding a fact EOC_TYPE table did work though. Thank you!