Forum Discussion

mjcuccia's avatar
mjcuccia
Frequent Visitor
7 years ago
Solved

hierarchy row counts

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 ...
Need Help
  • Nickgastaldi's avatar
    Nickgastaldi
    7 years ago

    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 :) 

Nickgastaldi's avatar
Nickgastaldi
Resolver I
7 years ago

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's avatar
mjcuccia
Frequent Visitor
7 years ago

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's avatar
    Nickgastaldi
    Resolver I
    7 years ago

    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's avatar
      mjcuccia
      Frequent Visitor
      7 years ago

      Crushed it!  Thanks Nickgastaldi!  Answer was like this:

       

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