Count of total number of elements at each level in the hierarchy.

[Kappa]

Hello!

The hierarchy is Sector > Subsector > Industrygroup > Industry > CanadianIndustries.

How to find the the total count values of elements at each level in the hierarchy?

For example, If we expand Finance and insurance :- 

There are:-

• 5 elements in the subsector, therefore besides Finance and insurance it shows the value 5.
• 3 elements in the industrygroups, therefore besides Credit intermediation and related activities,  it shows the value 3.
• 3 elements in the Industry, therefore besides Activities related to credit intermediation, it shows the value 3.
• 2 elements in the Canadian Industry, therefore besides Financial transaction .... activities it shows 2.
• And for the last level, it is the individual therefore the value is 1.

Please find the .pbix file for the reference.

https://we.tl/t-3QvT9ot3CV

Thanks

[SpartaBI · Accepted solution]

@Kappa write this measure:

Elements = 
SWITCH(
    TRUE(),
    ISINSCOPE('Hierarchical Data'[Canadian industry]), COUNTROWS('Hierarchical Data'),
    ISINSCOPE('Hierarchical Data'[Industry]), COUNTROWS('Hierarchical Data'),
    ISINSCOPE('Hierarchical Data'[IG]), DISTINCTCOUNT('Hierarchical Data'[Industry]),
    ISINSCOPE('Hierarchical Data'[Subsectors]), DISTINCTCOUNT('Hierarchical Data'[IG]),
    ISINSCOPE('Hierarchical Data'[Sectors]), DISTINCTCOUNT('Hierarchical Data'[Subsectors]),
    DISTINCTCOUNT('Hierarchical Data'[Sectors])
)

Showcase Report – Contoso By SpartaBI