4 Replies Latest reply on Dec 13, 2016 1:57 PM by Christina Davis

# average based on values in previous columns

Column AColumn BColumn C
112212345650.00
112245698750.00
113378956454.00
1144789523120.00
115578562489.00

Hi, I'm having trouble coming up with the formula that will say if there is a duplicate number in column A (i.e. 1122), take the average from Column C,  in this case 123456 is 50.00 and 456987 is 50.00, the average then for 1122 is 50.00. thank you

• ###### 1. Re: average based on values in previous columns

Use column A as Dimension and Take the Average of column C

2 of 2 people found this helpful
• ###### 2. Re: average based on values in previous columns

Hi Sabina,

You should be able to do this by creating a level of detail calculation. The below calculation averages C for each unique value of A while ignoring B hence giving you the average of any duplicates of A.

{EXCLUDE [B]: AVG([C])}

Hope this helps!

• ###### 3. Re: average based on values in previous columns

level of detail expression not supported by the federated datasource error

• ###### 4. Re: average based on values in previous columns

From what I've been able to find you might be able to get it to work by switching from a live data connection to an extract.

If that doesn't work then I can't think of another way of doing exactly what you asked. Your other option would be to use A as the dimension and then take the average of C as the measure as Gaurav stated but this will not allow you to include column B in your visualization.