Comparing average with the field

[#1]

Hi,
I have following data sample:

ID    Category    Value      Flag

  x         A                12      Chosen

  x         B                10      Cancelled

  x         C                 5      Converted

I need to compare average value (on id level) with that of value of category with 'Chosen' Flag

The output I need is this:

ID       Category         Value   Average Value

x             A                  12         9 (Average of 12,10 and 5)

I tried applying filter (Flag=Chosen), and ruled it out while calculating average. But, that does not seem to work:

avg({$<[Category]=,[Flag]=>}[Value])

This would ignore Category and Flag while calculating the average.

[marcus_sommer]

Try: avg({$<[Category]=,[Flag]=>} TOTAL [Value])

- Marcus

[MK_QSL]

=avg(TOTAL {<Category, Flag>}Value)

[#4]

Hi Marcus,
Thanks for the reply but isn't working.

[#5]

This would give the overall average (same value for each row on id level).

[MK_QSL]

If you are looking for different AVG based on ID use below

=avg(TOTAL <ID> {<Category, Flag>}Value)

[#7]

Hi Manish,
By using the above query, filter (Category=Chosen) cannot be applied, please help. Will any query be required for Id (As dimension and value) as well?

[MK_QSL]

Provide sample data along with the output you want in excel.