7 Replies Latest reply on Feb 25, 2017 9:54 AM by Ben Walsh

# Calculated Fields

Hi,

I have a basic question around applying a calculated field to all rows.

I want to group rows (by country), calculate the Average for one Country (Ireland) and then deduct this average from all countries. The issue is that since the average is only calculated for one country then the column "Total Minus Avg" column is missing values for the other countries as below.I understand this is because the table calculations are done across. I just don't know what the best solution would be.

CountrySalesAvgTotal minus Avg
Ireland1000200800
England500
France400

The ultimate goal is to create a bar chart and use the value "Total minus Avg" as my X axis to show countries above and below this line( as per sheet 3). I thought by creating calculated fields this may be the best solution but perhaps there is a better way.

Many thanks,

Ben

• ###### 1. Re: Calculated Fields

An LOD calc would work:

{fixed  [Country]: avg([Sales]) }

• ###### 2. Re: Calculated Fields

Hi Ben,

Are you expecting this;

This is how I did this

Let me know if this is what you are looking for?

Thanks

Deepak

• ###### 3. Re: Calculated Fields

Many thanks to you both.

I think we are nearly there.

However, I need the average for Ireland to be 500 - i.e  Total of 1500 divided by 3 Ireland records = 500.

Thanks again

• ###### 4. Re: Calculated Fields

Yes Ben,

It is done. There is a little change in Calculation 2. Calc1 and Calc3 are same.

Thanks

Deepak

Calc2:

{FIXEd [Country]: AVG([Calculation1]) }

• ###### 5. Re: Calculated Fields

Thank you Deepak.

Apologies if I am missing something but calc 2 looks exactly the same.

What I am expecting is this

Country          Sales         Cal1           Calc2           Calc 3

England          1100               0               500               600

France              800               0               500               300

Ireland             1500         1500               500             1000

Again many thanks for your replies and apologies if I am missing something.

Thanks,

Ben

• ###### 6. Re: Calculated Fields

Hi Ben,

Here it is now finally.

See Calcs Now:

Thanks

Deepak

2 of 2 people found this helpful
• ###### 7. Re: Calculated Fields

Thank you very much Deepak for helping me here so quickly.

It was a more complicated calculation than I had imagined.

Thanks again,

Ben