Different Measure Aggregations for a measure depending on the included dimensions

Question

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.

Could you please show the wished result? I'm not sure what you want instead. You can set IgnoreUnrelatedDimensions to false for NumberOfWalkInCustomers measure group in order to avoid replications. — Danylo Korostil
Thanks Danylo. I updated the description as best as I could. The purchase type dimension is not always unrelated. It doesn't affect the number of walk in customers but it affects the number of purchases made. I just need to display the Max within the Location Level but when I aggregate up on City level for example I would like it to be summed. — user3397375
It returns 300 for the Location1 instead, right? You need only 100, right? What should you get when you put the day level instead of week? Can you create a pseudo formula? For example: sum(week,sum(location,max(type, visits))) — Danylo Korostil
That's right. I need 100 for ALLPurchaseTypes( when the purchase type dimension is not added in the report). There's no day level. The facts always map to a weekstart date on the Date dimension. The formula that you created in fact explains the situation clearly. Thanks — user3397375

Danylo Korostil Danylo Korostil · Accepted Answer · 2017-09-16T11:23:51

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]
)

Different Measure Aggregations for a measure depending on the included dimensions

1 Answers