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