Search code examples
ssasmdxbusiness-intelligenceolapolap-cube

How to make a calculated member in MDX for SSAS?


I have a problem in my calculation and can't solve.

Example:

Our company sells about 100 products, divided into categories and product ID. Product ID is the smallest granularity. I need to show how this product list we sell in each city.

See the picture:

enter image description here

Overall, without showing the category it works well, however, when I show the product category it doesn't show how many products sold in that category, just repeats the full amount. And when I view the product ID, it returns the correct amount, which is equivalent to 1, follows the example pictures.

I used the following code, I will put generic way for better understanding.

COUNT(NONEMPTY([ProductDimension].[Category].[Product ID].Members,
               [Measures].[Valor Total]))

UPDATE 1

I made some changes, and it's working, however when I put in excel, and I use a Subcategory slicer or filter, and I just select a subcategory, it shows the right count for the selected subcategory, however if I select 2 or more, it shows the count total of all categories, not just the two or more selected.

I am using this code MDX:

COUNT((NONEMPTY([ItensRadiografia].[Referência].[Referência],[Measures].[Valor Total]),
                [ItensRadiografia].[Família].CURRENTMEMBER))

I have little knowledge, but I'm studying a book called "MDX Step by Step", however I need this response faster than'll get to read this book.


UPDATE 2

Follow the image:

enter image description here

The product dimension connects in the fact through ItemSK, which is the product code, the smallest granularity.

enter image description here

In the Dimension, Familia is a Category and Referencia is a ProductKey. The others are variations of how to write.

All hierarchies are composed Category and Product, except the last which is a test.


Solution

  • (all previous updates of answer were removed due to requirements discussion stage)

    Add Distinct Count measure on ItemSK field, it will solve the problem.