hierarchy row counts

[mjcuccia]

I have a need to get the row count for a particular level in my hierarchy to use that value in each child row of that hierarchy level.

In the example below, I would want the number of rows under the hierarchy level of 2017/Region: East/Location: Grove/Site: Grove.  Obviously, that count is 7.  I have struggled to get to that number since each of the OEM's under each site show up under multiple sites.  I have been getting an aggregate number including all rows which contain 'Lockheed Martin', for example.  In this case, I should only have a count of 1 for Lockheed Marting under this hierarchy level with the total aggregate of 7, and then I can use that count of 7 in a DAX formula for every row under this hierarchy level.  I would also need to use this type of row count for every iteration of this hierarchy level in the table at the OEM level under site.  Hope that makes sense!

[Nickgastaldi]

@mjcuccia

can you elaborate a little? 

the table you show is what you have or what you want? 

this calculations will be done in this table or are you building new measures for this ? 

help me understand, 

in your table, 

Site:Grove has OEM RANK = 1 

 but it should be 7 

is that your problem? 

[mjcuccia]

You can ignore OEM rank for the level at which Grove sits.  That rank only applies to the actaul OEM's which are under Grove.

I simply need on each row under Grove to acquire the row count of distinct OEM's only under Grove (this formula would be used for all other sites in the hierarchy as well), but in the example pic just Grove for now).  The count of distinct rows for Grove should come out to 7 to be used in a DAX formula on each row under Grove.

[Nickgastaldi · Accepted solution]

cant test to guarantee , but you can try something like this

hier-count = 
CALCULATE( DISTINCTCOUNT( mytable[id] )
                            ,ALLEXCEPT(myTable ,
                               myTable[Grove])
             )

hope this is understandable 🙂 

[mjcuccia]

Crushed it!  Thanks Nickgastaldi!  Answer was like this:

hier-count = 
CALCULATE( DISTINCTCOUNT( mytable[OEM] )
                            ,ALLEXCEPT(myTable ,
                               myTable[Site])
             )