Search code examples
ssasmdxolap

Mdx Queries calculate Percentage


I have the following msx query to calculate the percentage daily for the current month:

with member [Measures].[Percent] as ([Date].[Day].currentmember,
            [Measures].[Number of Calls]) / 
            ([Date].[Month].currentmember, [Measures].[Number of Calls])
            ,format_string = "percent"

select {[Measures].[Number of Calls],[Measures].[Percent]} on columns,

nonempty({[Date].[Month].children} * {[Date].[Day].children}) on rows 

from [Cube]

but this is not giving me the real percentage, it's giving me 100% for all records is there anything wrong in my query?


Solution

  • I have misunderstood the idea right here, I must have used [Date].[Day].currentmember.parent instead of [Date].[Month].currentmember, that's how it gives me the percentage against the dimension that comes before in the query that is [Date].[Month] the correct one will be:

    with member [Measures].[Percent] as ([Measures].[Number of Calls]) / 
            ([Date].[Day].currentmember.parent , [Measures].[Number of Calls])
            ,format_string = "percent"
    
    select {[Measures].[Number of Calls],[Measures].[Percent]} on columns,
    
    nonempty({[Date].[Month].children} * {[Date].[Day].children}) on rows 
    
    from [Cube]