Issue getting the total sale accounted by each store in MDX (in percent)

Question

I want to be able to create the following table in SSRS:

StoreKey    StorePercentage
1024        50 %
1037        20 %
1111        13 %
1949        27 %

where I've encountered that it was not possible to create a chart using Aggregated data. Hence I try to specify the StorePercentage value in SSAS (as I am using an analysis server as a data source in SSRS) and import that into SSRS and use the value off the bat. With the purpose of creating this data set I've started to test this query in SSMS:

With MEMBER [Measures].[StorePercentage] AS
    SUM (
            {
            [Dim Store2].[Store Key],[Dim Date].[Calender].[Date].[2014-11-23]:[Dim Date].[Calender].[Date].[2014-11-29]
            }, [Measures].[Total Price] 
        )
    / SUM
        ( 
            {
                [Dim Date].[Calender].[Date].[2014-11-23]:[Dim Date].[Calender].[Date].[2014-11-29]
            }, [Measures].[Total Sales]
        )
    Select non empty
        {
        [Measures].[Total Price],[Measures].[StorePercentage] 
        } on 0,
    [Dim Store2].[Store Key].children on 1
    FROM [theyseemecubintheyhatin]

But this gives me:

StoreKey    Total Price     StorePercentage
1024        50424           #Error
1037        3434            #Error
1111        1333            #Error
1949        2443            #Error

, i.e. my calculated measure seem to be problematic.

What I essentially try to do in the query, in the With MEMBER clause is:

SUM(Total sale made by each store and date range separately)/SUM(Total Sales for all stores over the time interval)

but I seem to be having some issues with this. I've tried a bunch of different combinations of {,( in the query but I haven't gotten it to work.

Does anyone have a clue? When I tried to Google this issue, the queries I found were related to getting data for a measure and a specific date range, but I need to query for each store as well.

Answer 1

I think it will make more sense:

With 
Member [Measures].[StorePercentage] AS
[Measures].[Total Price]
/
([Dim Store2].[Store Key].[All],[Measures].[Total Price])

select 
non empty {[Measures].[Total Price],[Measures].[StorePercentage]} on 0,
non empty [Dim Store2].[Store Key].[Store Key].Members on 1
from [theyseemecubintheyhatin]
where ({[Dim Date].[Calender].[Date].[2014-11-23]:[Dim Date].[Calender].[Date].[2014-11-29]})

The problem code is here:

{[Dim Store2].[Store Key],[Dim Date].[Calender].[Date].[2014-11-23]:[Dim Date].[Calender].[Date].[2014-11-29]}

Because [Dim Store2].[Store Key] doesn't mean anything.