jmfreier avatar image
jmfreier asked

SSAS MDX Calculated Member Grand Total Issue

I've been working on this issue now for quite some time... In one of our cubes we have a dimension with a hierarchy that basically breaks apart ad units by type (legacy, rich). We have calculated members that we use to analyze performance. We now need to filter these so that certain calculated members only perform on the rich type. I have most of the re-write figure out, but two things happen: 1. When pulling the ad unit type out of the pivot table in Excel, and moving it to the filter section, it completely removes the information from the subtotal/grand total 2. The grand total is always wrong unless you do not filter the ad unit type of rich to multiselect. This all seems related to multiselect and how to aggregate only the rich ad unit types that are selected into the grand total.. I've tried doing this with scope statements and it seems to be somewhat closer to what we need, but I'm still running into these problems. Is there anyone out there willing to assist in helping me figure out how to get this working? Currently my mdx is this (Sorry for the formatting in advance...): create member currentcube.[measures].[rich revenue] as null; scope( [ad unit].[ad unit].members, [measures].[rich revenue]); this = iif( ([ad unit].[ad unit].currentmember is [ad unit].[ad unit].[ad unit type].[rich]) or ([ad unit].[ad unit].currentmember.parent is [ad unit].[ad unit].[ad unit type].[rich]), divide([measures].[revenue],[measures].[offers]), iif( [ad unit].[ad unit].currentmember is [ad unit].[ad unit].[all], divide( ([ad unit].[ad unit].[rich], [measures].[revenue]), ([ad unit].[ad unit].[rich], [measures].[offers])), null ) ); end scope;
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.