CODEWITHSUNDEEP
ssasmdxaggregation
user3397375
user3397375

Reputation: 1

Different Measure Aggregations for a measure depending on the included dimensions

I'm having trouble with the aggregation of a measure. The real world scenario is that the customer wants to analyze ratio of number of purchases made over the number of customers entered the store for that week. We know how many customers entered for each week and how many purchases were made along with the Purchase Types (Expensive, moderate, cheap). Because there are different Purchase Types, multiple rows are created for that location and for that week. Therefore same measure (NumberOfWalkInCustomers) is replicated.

These are the records. NumberOfWalkInCustomers are the totals per the Location and the WeekStartDate only. The duplicated rows are the result of the purchase Types. Without any dimensions in place, the sum looks like 850 but I would like to see the total as 650 because that's the total of Customers entered to those two locations in those two weeks.

What i would like to see is when I aggregate within the location and the date dimensions, the measure would get summed up. How can I avoid the sum aggregation when other dimensions are in place?

If we have Date, Location and Purchase Type dimensions all in the report, it looks okay But when I remove the Purchase Type Dimension, I want the Location1 to show still 100 because Purchase type doesn't affect the number of customers entered to a location. However, when I remove Date Dimension, I would like to see 250 (100 from Week1 and 150 from Week2 added up) for Location1. The website allows me to add only two links. Sorry.

Upvotes: 0

Views: 441

Answers (1)

Danylo Korostil
Danylo Korostil

Reputation: 1484

There are two ways: slow and fast.

1 (Pure MDX):

With 
[Measures].[SumWeekStartDateSumLocationMaxPurchaseType] as
Sum(
      existing [Date].[Week Start Date].[Week Start Date].Members,
      [Measures].[2]
)


[Measures].[SumLocationMaxPurchaseType] as
Sum(
      existing [Store Location].[Location].[Location].Members,
      [Measures].[MaxPurchaseType]
)

[Measures].[MaxPurchaseType] as
Max(
      existing [Purchase Type].[Purchase Type].[Purchase Type].Members,
      [Measures].[Number Of Walk In Customers]
) 

select 
[Measures].[SumWeekStartDateSumLocationMaxPurchaseType] on 0
from [YourCube]

However, for big dimensions you may find it really slow.

2 (A bit of MDX + DWH): Add a new measure to your cube with the following structure:

select 
      WeekStartDate,
      Location,
      MaxWalkIn = max(WalkIn)
from FactTableWalkIn
group by WeekStartDate, Location

Add the MaxWalkIn measure with sum aggregation + calculated measure:

IIF(
      [Purchase Type].[Purchase Type].CurrentMember is [Purchase Type].[Purchase Type].DefaultMember,
      [Measures].[MaxWalkIn],
      [Measures].[Number Of Walk In Customers]
)

Upvotes: 1

Related Questions