Count and Display Participants on Hierarchy Level

I try to count / aggregate the number of participants of the hierarchy level (422) and replace the total amount of participants (659).
I do not know how. Can somebody help me please?
Thanks.

Does anyone has a clue how to solve that? Thanks!

Do I understand this correctly? You are using several calculated members from dimension Participants in the Pie chart. You would like to count participants there?

You would like to use a custom formula to count participants for each selection:

Sum(
Generate( 
      { [Participants].CurrentHierarchyMember,
      ChildrenSet([Participants].CurrentHierarchyMember) },
      Descendants([Participants].CurrentHierarchyMember,[Participants].CurrentHierarchy.Levels('Participants'))),
   CASE WHEN 
   -- check if the user is involved in any issue selected for report
   [Measures].[Issues created] > 0
   THEN 
   -- count each user as 1
   1 
   END
   )

Please note, you might get duplicated count for measure count of participants if any issue is involved in several slices. The pie chart does not use a distinct count. It sums values from all slices.

If users are involved in several groups you would like to use another chart and use member All Participants with a measure count of participants with the formula I shared above.

Here is my example with two calculated members with assignees. One assignee is involved in both groups. The pie chart gives a value 5 for the count of assignees. If I switch this chart to the table and add All Assignee it gives me 4 as a count of involved assignees that is the correct value.

Here is a table report where I am using All Assignees to see the total correct count of involved assignees:

Daina / support@eazybi.com

Obviously there are only about 230 Participants

I tried it out (added your formula to “TestAttendees”)

The measures to be checked are quite simple

I would like to have one chart showing the participants per measure (like Session A - Basics).
Furthermore I would like to have on chart showing only the participants for all measures in total, each participant counted once.

Thanks for you help!!!

Sum(
Generate(
{ [Participants].CurrentHierarchyMember,
ChildrenSet([Participants].CurrentHierarchyMember) },
Descendants([Participants].CurrentHierarchyMember,[Participants].CurrentHierarchy.Levels(‘Participants’))),
CASE WHEN
– check if the user is involved in any issue selected for report
[Measures].[Session B - abc]>0 OR [Measures].[Session A - Basics]>0 OR [Measures].[Session B - Fundamentals]>0 OR [Measures].[Session B - def]>0 OR [Measures].[Session B - ghi]>0 OR [Measures].[Other Session]>0
THEN
– count each user as 1
1
END
)

helped me. Thanks

1 Like