Sunday, August 3, 2014

Percentage of one level up

One of the most common calculations is calculating the percentage of the current value to the total one level up. For instance [Measures].[Sales Amount] for the quarter compared to the semester.
This gives use several challenge's.
1. What is the total amount one level up. With the .CurrentMember property we know where we are. If we combine this with the .Parent property we can calculate the total amount: ([Measures].[Sales Amount],[Date].[Calendar].CurrentMember.Parent) .
2. We want to avoid a division by null, there might be no sales for a specific period. We can use the IsEmpty functionality for that:
WHEN IsEmpty
(
    [Measures].[Internet Sales Amount]
)
THEN NULL
3. We want to be sure there is one level up. For the [(All)] level there is no level above. Also a calculation on the [(All)] might not make any sense.
WHEN [Date].[Calendar].CurrentMember.Level Is
    [Date].[Calendar].[(All)]
Then 1 // or NULL
If we combine all this together we get:
WITH MEMBER [Measures].[Percentage of Whole CY]
// This Will only give the correct results when
// used together with Date.Calendar hierarchy
// for other hierarchy adjust accordantly
AS
CASE
// Test to avoid division by zero.
WHEN IsEmpty
(
    [Measures].[Internet Sales Amount]
)
THEN NULL
// Test for current coordinate being on the (All) member.
// you might want replace the 1 with NULL if a percentage
// of the (All) level doesn't make any logical sense.
WHEN [Date].[Calendar].CurrentMember.Level Is
    [Date].[Calendar].[(All)]
Then 1 // or NULL
ELSE (
// The actual calculation
    [Measures].[Sales Amount]
    /
    ([Measures].[Sales Amount],[Date].[Calendar].CurrentMember.Parent)
)
END
, format_string='0.00%'
// Format it as percentage
SELECT
NON EMPTY
(
    {
      [Date].[Calendar].[All Periods]
     ,[Date].[Calendar].[Calendar Year].&[2013]
     ,[Date].[Calendar].[Calendar Semester].&[2013]&[2]
     ,[Date].[Calendar].[Calendar Quarter].&[2013]&[3]
    }
) ON COLUMNS
,
{
     [Measures].[Sales Amount]
    ,[Measures].[Percentage of Whole CY] }ON ROWS
FROM [Adventure Works]
image
Since a percentage for All Periods doesn’t make any sense, this one is better:
image
Have Fun And Till Next Time

No comments:

Post a Comment