I've been trying to solve this query for a while but I don't understand where I'm wrong. The questions of the query is this one:
Show the winners having a total winner rank points greater than the average winner rank points in each continent by continent and year
The query I have thought to solve this problem is the following:
with member media as sum(([Tournament].[Tourney Id].CURRENTMEMBER,
[Loser].[Player Id].allmembers),
[Measures].[Winner Rank Points])/ [Measures].[Conteggio di Match] member loserrank as
([Tournament].[Tourney Id].CURRENTMEMBER, [Measures].[Loser Rank Points]) member
ratio as case when media = 0 then 0 else loserrank / media end select
{media, loserrank, ratio} on columns,
GENERATE([Tournament].[Tourney Id].[Tourney Id].members,
TOPCOUNT(([Tournament].[Tourney Id].CURRENTMEMBER,
nonempty(FILTER([Loser].[Player Id].[Player Id], [Measures].[Loser Rank Points] >0
))), 1, ratio)) on rows
from [DBXMLCD]
The result of this query is this: [Query result]
Media means that it is the average, the values of media and loserrank that come out to me are wrong because i ran a query in sql and the correct results obtained are different from these here and i think the problem is on the average calculation (media).
I hope you can give me a hand, thanks in advance, and for other doubts write me here and I will reply immediately.
