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:
This would ignore Category and Flag while calculating the average.
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?