Sum of Column for each sub dimension

Questions & Answers
1

Hi, i would like to get the results as shown in the image. I am trying to get the sum of Column X in Column Y for each “All Sub Team”.

Column X is a case when statement for Status with “Sustainable”. The MDX is as such:

(case when SetToStr([Status].CurrentMember) = “{[Status].[Sustainable]}”
then 1
END)

Column Y is trying to get the sum of “Sustainable” for each Sub Team. The MDX is as such:

sum(
Descendants([Sub Team].CurrentMember, [Sub Team].[Sub Team]),
[Measures].[Column X]
)

image
image1363×793 56.1 KB

Would appreciate help on this. Thank you!

2

Hi @whosiao ,
Here is a formula that you can use in your report without the “Status” dimension. It counts how many “Sub Team” members have the status “In progress”.

NonZero(
  Count(
    Filter(
      Descendants([Sub Team].CurrentMember,[Sub Team].[Sub Team]),
      ([Measures].[Issues created],
      [Status].[In Progress])>0
    )
  )
)

Kind regards,

Gerda // support@eazyBI.com