Partial sums not working correctly for all columns

[SGood]

I have a table with a number of columns, partial sum is working for some but not for others.  I believe I need to use Aggr, but having tried various forms I can't seem to get it right.

Basic Formula:

=if([Ref_Gov]='Governed',
sum({<[cost_type_2023]={'Hardware'}>}[FY_Forecast_2023])/1000,
sum({<[cost_type_2023]={'Hardware'},[L4_2023]={'FDRRI'}>}[FY_Forecast_2023])/1000)

In english, if the record is 'Governed, then I want the total of  ALL records where the cost type is Hardware, if not governed then only the total of Hardware where the L4_2023 is equal to FDRRI.  Any help would be much appreciated.  

The above calc works for the individual records, but not for the partial sums row.

[Or]

Move the if() statement inside the sum() statements, rather than vice versa.

[SGood]

Hi, 

If statements are normally structured, IF criteria, then ..., or ...... 

It's unclear to me how I would do this, could you give me an example?

Thanks.

[Or · Accepted solution]

A tad tricky in this case since each individual sum has its own set analysis, but generally speaking,

Sum(If(Condition,Field1,FIeld2))

When you use the if() outside, it is evaluated individually for each row, which means each row in your data is returning a sum of itself, and the result of that is a bunch of numbers that aren't aggregated.

There's likely a clean way to do this with the set analysis but a quick fix would be along the lines of:

sum({<[cost_type_2023]={'Hardware'}>}[FY_Forecast_2023]/1000 * if([Ref_Gov]='Governed',1))

+

sum({<[cost_type_2023]={'Hardware'},[L4_2023]={'FDRRI'}>} [FY_Forecast_2023]/1000*if([Ref_Gov]='Governed',null(),1) )

That is, within each sum, multiply by null for any line that doesn't match the appropriate side of the if() statement.

[SGood]

This has worked and has helped me resolve the same issue for other columns.  Thank you, so much.