I have a problem in my calculation and can't solve.
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:
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]))
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.
Follow the image:
The product dimension connects in the fact through ItemSK
, which is the product code, the smallest granularity.
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.
(all previous updates of answer were removed due to requirements discussion stage)
Add Distinct Count measure on ItemSK
field, it will solve the problem.