Filtered Total Calculated Measure

Question

Post reply

Filtered Total Calculated Measure

mvee

Hall of Fame

Points: 3634
More actions
June 3, 2014 at 11:37 am

#148539

I'm trying to create a calculated measure that gets the filtered total on each row (to be used in additional calculated measures).
More Info:
e.g.
Let's say I set some filters on my cube/pivottable and then pull in the Sales Date hierarchy (year, quarter, month, date) onto rows and I pull Inventory Count as a value
Then I expand Sales Date to Month and I filter Sales Date to show year 2013.
Now I want to pull a new calculated measure "TOTAL Inventory Count" into values and I want it to show the TOTAL of 2013 on each row.
If I was using Excel, I could use some form of GetPivotData, but I'm actually not using excel, we use a 3rd party viewer, so this won't work for me.
I've tried creating a Scoped measure, e.g.:
CREATE MEMBER CURRENTCUBE.[Measures].[TOTAL Inventory Count]
AS Sum([Measures].[Inventory Count]),
FORMAT_STRING = "#,#",
NON_EMPTY_BEHAVIOR = { [Inventory Count] },
VISIBLE = 1;
SCOPE([TOTAL Inventory Count],[Sales Date].[Sales Date].MEMBERS);
THIS = Sum([Measures].[Inventory Count],[Sales Date].[Sales Date].[All]);
END SCOPE;
But this returns the TOTAL, Total Inventory Count for All Sales Dates, I want the total for only 2013 (the selected filtered which the user could change)
See attachment (with slightly different names) - I want all Total TGW Count values to have the Grand Total from TGW Count - when The row labels have been filtered.
Please help?! Any Suggestions?!
Thank you, Megan

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

PB_BI SSCoach Points: 17358 More actions · Answer 1

I'm not sure of your hierarchy structure but something like this would do the trick:

CREATE MEMBER CURRENTCUBE.[Measures].[TOTAL Inventory Count]

AS SUM( ANCESTOR([Sales Date].[Sales Date].CURRENTMEMBER,

[Sales Date].[Sales Date].[Year]),[Measures].[Inventory Count])

I'm on LinkedIn

mvee Hall of Fame Points: 3634 More actions · Answer 2

Thank you for the response.

This gives the total of the year, yes, but what if I've filtered to only quarters 1 and 2 in year 2013?

The total is the year, but I want to get the filtered total?

Or, what if I've filtered on years 2013 AND 2014. The grand total in the left column will be the sum of both the years and THAT is the value on want in my new column...

See attachment.

Thank you, Megan

mvee Hall of Fame Points: 3634 More actions · Answer 3

Using these posts:

http://social.msdn.microsoft.com/forums/sqlserver/en-US/551dad5d-76d1-4809-bca7-0a9d3aca5cd8/calculate-the-total-of-a-filtered-set-of-data

http://www.purplefrogsystems.com/blog/2009/11/scope-problems-with-mdx-calculated-members/

I finally got it working with the following code:

CREATE DYNAMIC SET [Set ManufactureDate Current Scope]

AS [ManufactureDate].[Manufacture Date Yr - Qtr - Mnth - Dt].[Date];

CREATE DYNAMIC SET [Set SalesDate Current Scope]

AS [SalesDate].[Sales Date Yr - Qtr - Mnth - Dt].[Date];

CREATE MEMBER CURRENTCUBE.[Measures].[Total Inventory Count by Date]

AS Null,

FORMAT_STRING = "#,#",

NON_EMPTY_BEHAVIOR = { [Inventory Count] },

VISIBLE = 1;

SCOPE(DESCENDANTS([ManufactureDate].[Manufacture Date Yr - Qtr - Mnth - Dt],,AFTER),[MEASURES].[Total Inventory Count by Date]);

THIS = Sum([Set ManufactureDate Current Scope],[Measures].[Inventory Count]);

END SCOPE;

SCOPE(DESCENDANTS([SalesDate].[Sales Date Yr - Qtr - Mnth - Dt],,AFTER),[MEASURES].[Total Inventory Count by Date]);

THIS = Sum([Set SalesDate Current Scope],[Measures].[Inventory Count]);

END SCOPE;

Good luck to others having similar issues. Do not give up!