1

I'm using excel 2013 and need some assistance with a few CUBE function needs and the proper MDX needed to resolve my issue. I would like to exclude from one MDX set, the names in another to create a "NOT IN" scenario. I have a hierarchy table with a list of representatives and sales table with representatives that would also be in the hierarchy, but that have sales.

That's problem A:

Problem B and C are: I'd like to be able to do the same as problem A but further filter down to those who have sales above a certain amount. Also is there a way to return an AVG or SUM from cubevalue without having to set up a DAX measure in powerpivot?

Lastly, I'd like to be able to use TOPCOUNT or BOTTOMCOUNT to create my top/bottom N reports. Thanks for your help in advance.

DCSlick
  • 19
  • 1
  • The two dimensions for A: [Hierarchy].[Representatives].children and [Sales].[Representatives].children. Hierarchy will have all representatives names that COULD have sales. Sales table has ONLY those who have made a sale. I'm wanting to show those who have NOT made a sale. In SQL i would simply do an ANTI JOIN or a NOT IN. I don't know how to return a set in MDX of those without a sale. Two separate tables. Once I understand how to have a NOT IN set, then i can figure out the bottomcount or topcount from there.Sorry for not including everything, when you're new you learn what to include. – DCSlick Oct 22 '16 at 14:24

0 Answers0