HELP ! URGENT! Need help in finding the row count for the Team grouped by

Questions & Answers
, ,
1

How to count the total number of issues based on the team name shown in the first column? For Example, Clairvoyants team has 2 defects and I want that 2 to be displayed on TEAMSUM column and likewise Defenders team , the last column should show 5.

I am trying multiple options but unable to get that count. I am trying to understanding total number of ageing defects for a team based on certain filter conditions already set.
NOTE: I cannot remove any of these columns due to the conditions I set already.

image
image782Ă—624 48.2 KB

2

Hi @subintraj

The solution you are looking for is Tuple expression. By using DefaultMember() function you can ignore the individual issues on report rows and enable the formula to sum the issues only by Teams.
Note that in the example below I assumed your Dimension name (first column) is “Team”. If it’s different, please update the formula accordingly.

([Team].CurrentHierarchyMember,
[Issue].CurrentHierarchy.DefaultMember,
[Measures].[Issues created])

Please let me know if you have further questions regarding this!

Kind regards,
Elita from support@eazybi.com

1 Like
3

Thanks, Elita. That worked but with a problem there. I tried to put a cell formatting filter on the Aeging column, say >= 460 days only to show. So for ex, after applying that filter for that column, the Defenders team showed 5 instead of 1 in the column TEAMSUM. Wouldn’t this formula consider what is being displayed and based on that filter and take the count?

4

Hello @subintraj

The reason the formula still returns same number even if you filter your report, is because DefaultMember context is used for Issues dimension in Tuple expression, in order to sum up all issues for the Team.

If now your goal is to count issues based on a certain criteria, a different approach should be used.
I recommend using Descendants() function to iterate through issues and filter out the issues where “Ageing in Current Status [Days]” is larger or equal to 460.

Try using below formula and see if this is what you are looking for?
If you want to see the total amount of issues for the particular team, I recommend switching to Project level in Issue dimension.

Sum(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    [Measures].[Ageing In Current Status [Days]]] >= 460
  ),
  CASE
  WHEN 
  ([Team].CurrentHierarchyMember,
  [Issue].CurrentHierarchy.DefaultMember,
  [Measures].[Ageing In Current Status [Days]]])>0
  THEN
  1
  END
 )

Best wishes,
Elita from support@eazyBI.com