Search code examples
sql-serverssasmdxolap

how to calculate average when some rows does not exist?

Please help to create average when some values are NULL

fact table:

enter image description here

cube:

SELECT NON EMPTY {[Measures].[Score]} * [Date].[Month].allmembers ON COLUMNS
,{[Name].[name].allmembers} ON ROWS
FROM Test

enter image description here

problem:

when I calculate average, NULL values are excluded. I tried COALESCEEMPTY(), but did not manage to calculate average correctly anyway. Average for months where Score=0 is not correct. Heres the code:

WITH
MEMBER [Measures].[DateCount] AS DISTINCTCOUNT([Data].[date].[date])
MEMBER [Measures].[ScoreX] AS COALESCEEMPTY([Measures].[Score],0)
MEMBER [Measures].[DateCountX] AS COALESCEEMPTY([Measures].[DateCount],0)
MEMBER [Measures].[AvgScore] AS IIF([Measures].[DateCountX]=0,0,[Measures].[ScoreX]/[Measures].[DateCountX])
 SELECT NON EMPTY {[Measures].[AvgScore]} * [Date].[Month].allmembers ON COLUMNS
 ,{[Name].[name].allmembers} ON ROWS
 FROM Test

enter image description here

Please help find the solution.

Solution

  • the final solution was this:

    WITH

MEMBER Measures.[AvgScore] AS
Avg(
Descendants(
[Data].[Date].CurrentMember,
[Data].[Date].[Date]
),
coalesceempty(Measures.[Score],0)
)

 SELECT NON EMPTY {[Measures].[AvgScore]} * [Date].[Month].allmembers ON COLUMNS
 ,{[Name].[name].allmembers} ON ROWS

FROM Test