SSAS Measure average related to range values

Question

I have Sales data provided weekly and Lookup data provided quarterly. In the SSAS data cube I have pre-calculated average of sales data for each period of time and what I need to do is to get related record from LookupTable for next calculations, where: LookupTable.Min < Sales Average < LookupTable.Max

Example:

Sales = 297 + 33 + 311 = 641

SalesAverage = 213.66

LookupRecordShrinkageIndicator = Min < SalesAverage < Max = 0 < 213.66 < 9000 = 0.007

CREATE TABLE dbo.SalesData
(
    Id int,
    Sales decimal(18, 2)    )


CREATE TABLE dbo.LookupTable
(
    Id int,
    Min int,
    Max int,
    Shrinkage decimal(10, 5),
    Wages decimal(10, 5),
    Waste decimal(10, 5)
 )

INSERT [dbo].[SalesData] ([Id], [Sales]) VALUES (1, 297)
INSERT [dbo].[SalesData] ([Id], [Sales]) VALUES (2, 33)
INSERT [dbo].[SalesData] ([Id], [Sales]) VALUES (3, 311)

INSERT [dbo].[LookupTable] ([Id], [Min], [Max], [Shrinkage], [Wages], [Waste]) VALUES (1, 0, 9000, 0.00700, 0.12700, 0.00300)
INSERT [dbo].[LookupTable] ([Id], [Min], [Max], [Shrinkage], [Wages], [Waste]) VALUES (2, 9000, 9250, 0.00700, 0.12700, 0.00300)
INSERT [dbo].[LookupTable] ([Id], [Min], [Max], [Shrinkage], [Wages], [Waste]) VALUES (3, 9250, 9500, 0.00700, 0.12300, 0.00300)

I need to create calculated member based on sales average which contains indicators from lookup table for next calculations.

I am not sure what your expected output should be and how it is calculated. — juergen d
I need create Calculated Member based on Sales Average. In example I need to adjust the value to the range from LookupTabe. LookupTable contains columns Min and Max and I need to find values from it where Sales Average is between Min and Max. The Calculated Member should be in example value of Shrinkage column from this table. You should also know that LookupTable cannot be aggregated, I need pure values from this table matched on the basis of sales average. I hope that helps you together with examples above. — wbargiel

wbargiel wbargiel · Accepted Answer · 2013-10-18T09:47:06

To solve this issue I had to use my LookupTable as dimension and as measures, let's see how I did this.

Create dimension based on LookupTable:
Add Lookup measures do the cube and add Lookup dimension to the cube as well.
Create Fact relationship between Lookup dimension and Lookup measures group

That's all:

Let's see mdx example:

SELECT 
{
    FILTER([Lookup Table].[Id].AllMembers ,  [Measures].[Min] <= 213 AND [Measures].[Max] > 213 )
}
ON COLUMNS,
{
    [Measures].[Shrinkage - Lookup Table], [Measures].[Wages - Lookup Table], [Measures].[Waste - Lookup Table]

} ON ROWS
FROM
[MyCube]

And result:

MDX Query result

I hope this example will be useful

SSAS Measure average related to range values

1 Answers