I have an issue with the average function and set analysis I would like to share with you and if possible get some help.
The issue is as follows:
1 - I have to calculate an average of the distinct unit prices for each Substance, Group combinations
2 - However, if there are more than 5 distinct unit prices the average should only compute using the 5 lowest unit prices calculated amongst the different products within each substance, group
3 - If the number of distinct unit prices per substance group combination is lower than or equal to 5 then all unit prices should be considered in the average calculation
It should not be a big deal, however I'm not having the accurate results.
There are 2 main issues:
a) the average is not calculated accuratelly when there are more than 5 distinct unit prices (Conditional Average in the QVW table attached)
b) only when a specific group is selected (out of the ones with more than 5 distinct unit prices) the result is accuratelly fetched.
I appreciate if you could take a look on the QVW attached and let me know what the hell I'm not doing in order to get the proper results.