SSAS MDX Calculated Measure - COUNT of [ITEMS].[Item] grouped [Items].[Item Group]

Question

In Excel connected to SSAS, I am trying to build a pivot table and add a custom Measure Calculation using "OLAP Tools" and/or "OLAP Pivot Table Exensions". I am trying to add a calculation that is really simple in my mind, but I cannot get it to work. The calc I need is:

GOAL: A record count of the [Items] dimension records grouped by any of the [Items] dimension fields.

In particular I am trying to group by [Items].[Items Groups] and [Items].[Item]. Item is the lowest grain, so the count should return value "1". I have created a couple calculations that are kind of in the ballpark (see below). But the calcs don't appears to be working as desired.

What I have tried:

Attempt #1 -- [Measures].[Items Count (With net amount values)]

DISTINCTCOUNT( {[Items].[Item].MEMBERS} )

The calc 'Items Count (With net amount values)' appears to be returning a decent count value, but it appears it only counts the Item if there are transnational records found (not sure why). Also, when at the lowest grain level the calc returns that value for the parent group, not the dimension level selected on the rows.

Attempt #2 -- [Measures].[Items Count (All)]

[Items].[Item].[Item].Count

This calc returns the TOTAL item count for the entire dimension regardless of the dimension level placed on the rows.

Attempt #3 -- [Measures].[Items Count]

COUNT ( { [Items].[Item].MEMBERS}, EXCLUDEEMPTY)

This calc freezes up Excel and I have to quit Excel. No idea why. I have seen this sytnax recommended on a few different sites.

Screenshot:

Help please? This seems really simple, but I am not very skilled with MDX. In DAX and SSAS TABULAR this would be very simple expression. But I'm struggling to count the rows with MDX in SSAS MD.

The "Outside Purchased Beef" group has 18 items with transactions, but 41 items in total. I do not know how to calculate the "41" value.

SSAS Excel-CalcMeasure-CountRows.png

Answer 1

Take a look at the following samples on AdventureWorks.

with member [Measures].[CountTest]
as 
count(existing  [Product].[Subcategory].members - [Product].[Subcategory].[All])
select 
{
[Measures].[Internet Sales Amount],[Measures].[CountTest]
}
on columns,
{
([Product].[Category].[Category]
,[Product].[Subcategory].[Subcategory] -- comment this line for the second result
)
}
on rows 
from [Adventure Works]

Now comment the indicated line for the parent view.