Deriving a new Field based on some other field condition

[psingh206]

Is there any way of deriving new field in the Table based on a condition from other column.Suppose we have the first two columns as Code and Price and we want to derive the third column as Average Price which represents the average of Price of a particular code. For example, if I need to calculate average of code '1'. It should return (500+150+300)/3 = 316.67 

[sunny_talwar]

I am assuming that you need this on the front end of a chart object try this

Avg(TOTAL <Code> Price)

[wizardo]

try this

MAIN:
LOAD * INLINE [
CODE, PRICE
1, 500
2, 200
2, 300
3, 450
1, 150
2, 200
1, 300
3, 100
];

left join

load
CODE,
avg(PRICE) as Avg_Price
Resident MAIN
Group by CODE;

[psingh206]

@sunny_talwar When I define this expression as a measure of the table, and filter the data with filter pane , the averages now calculated are based on the selection made. Is there any way so that this measure is not affected by Filtering ?

[wizardo]

Avg({<Fieldname1=,fieldname2=>} TOTAL <Code> Price)

put all the fields from the filterpanes you want to ignore 

[psingh206]

@wizardo This blocks the filtering based on the fieldname mentioned. I need filtering to be performed but don't want my averages to be calculated based on selection. In other words, selection should not be considered while calculating averages but filtering should be run. Average should be calculated irrespective of any group.

For example, I have this data :

Now, I filter my data with respect to Group 'A'.

Desired output :

Obtained output :

[sunny_talwar]

Try this

Avg({1} TOTAL <Code> Price) * Avg(1)

[psingh206]

@sunny_talwar Not working . First of all it is blocking the filtering and also it is making all the other rows '-' when any selection is made. 

[sunny_talwar · Accepted solution]

Isn't this what you wanted?

I think you might have forgotten to uncheck 'Include Zero values' under Add-Ons - > Data Handling

[psingh206]

@sunny_talwar Yeah i forgot to uncheck that....Thanks a lot !!