Help with Calculated Field/ Totals & Aggregations

[Aleksandra]

Hello,

I am unsure how to approach this, so I hope I'll get some ideas from some forum members. I have a table where I track Resource Earnings. There are 3 fields for earnings: Project Earnings, Fixed Earnings, and Bonuses. There is a date field next to each of earning type which tracks when the earning was made. What I would like to do is to track the total monthly and yearly earnings per employee. 

I tried to do this by adding a calculated field with the following formula: SELECT SUM (IsNull([Project_Earnings], 0) +IsNull([Fixed_Earnings], 0) +IsNull([Bonus], 0)) FROM AM_tbl_Resources_Earnings WHERE [Resource_ID] = .target[Resource_ID] AND Year([Date_1])=2022 AND Year([Date_2])=2022 AND Year([Date_3])=2022.

This formula gives me the correct result but the thing is that I see that result in the tabular report as a separate column and I want to see the total yearly earnings per employee in the bottom row as total. Or, even better, I would like to see it as in the screenshot below for total revenue, like a separate value. 

I tried to create a separate column Total Earnings that would give me a sum of Project Earnings, Fixed Earnings, and Bonus and eventually work with that field, but for some reason, my formula is not working. All field types that refer to earnings are currency.

Thank you all in advance.

[Aleksandra]

Just to add to the tread, I don't need to have a visualization like in the screenshot I sent.  I need to see the total yearly earnings per resource in the table, but I would like to see it in the total field. When I try to apply this formula in totals and aggregations, it doesn't work, but it works when I apply it in the Calculated field. But I get an additional column, and I don't want it.

I also noticed I made a mistake in a part of the formula, it is [@filed:Resource_ID] = target.[@field:Resource_ID].

Also, does anyone know what could be a reason for not getting Total Earnings when I try to create this as a column?  I've tried to create a calculated field with a simple formula: [@field:Project_Earnings]+[@field:Fixed_Earnings]+[@field:Bonus] but it doesn't show any value. I've also tried to create this field as a part of the table by selecting Formula as a field type, but I didn't get any value in that field either.

Thank you all in advance.

Aleksandra

[Aleksandra]

As I needed the amount on yearly level, I've replaced the formula above with this one:

CASE
WHEN Year([@field:Date_1]) = 2022 OR Year([@field:Date_2]) = 2022 OR Year([@field:Date_3])=2022
THEN
(SELECT SUM ((IsNull ([@field:Project_Earnings], 0) +IsNull ([@field:Fixed_Earnings], 0) +IsNull ([@field:Bonus], 0))) FROM AM_tbl_Resources_Earnings WHERE [@field:Resource_ID]= target.[@field:Resource_ID])
END

It gives me correct results, but still I see that in the form of separate row with the same values. I've attached a screenshot of the report. What I don't understand is why I cannot get total value by simple formula Project Earnings + Fixed Earnings + Bonus? I would prefer to get that value in the form of a separate column and then create a total & aggregation field based on it.