I have a report that shows the assignee, task and the percentage complete (<100 or percentage due). I want the total row count for the rows that I see in the report. When I use the total under the table selection, it sums up all the percentages and gives me a total percentage. I only want the row count which tell me how many issues have percentage <100
Hi,
the standard Total function is not parameterizable, it automatically sums up or calculates average or percentage, depending on measure selected in each particular column.
As a workaround, you can create a new calculated measure which counts issues with percentage less than 100%:
NonZero( Count( Filter(Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]), [Measures].[complete %]<1) ) )
(change [Measures].[complete %] to correct measure name; set formatting "Integer").
Select this measure in columns; then Total row, as well as higher level Issue dimension members, will show number of issues with completeness less than 100%
If it doesn't help or more specific answer is needed, please, export and send report definition to support@eazybi.com
Ilze / support@eazybi.com
I created a user defined field (count percentage due) with the code you sent but it doesnt work, please see the screen shot, i get the same percentage total in rows and columns but no rowcount.
NonZero(
Count(
Filter(Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]), [Measures].[Percentage complete]<100)
)
)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thank you. I managed to get the count but how do I avoid it showing the percentage sum at the bottom? Its kind of silly to show total percentage. Also, is there a way to avoid the column with 1s and just show total?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi again,
In eazyBI, each measure selected in columns have its own formatting, therefore, if a percentage is shown in rows, it is not possible to show integer (which is needed for count) in the Total row or in higher level rows. We can not avoid creating an additional measure for that.
If you do not want to use Total function at all, you can display this count in Issue dimension Project level row, and hide values in issue rows (they will stay empty), see screenshot:
eazyBI_JIRA_plugin.png
To do that, modify the formula given in the previous answer to display values only on Issue dimension Project level rows:
CASE WHEN [Issue].CurrentMember.Level.Name = "Project" THEN NonZero(Count( Filter(Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]), [Measures].[Complete %]<1) )) END
Again, change [Measures].[Complete %] to your measure.
Ilze /support@eazybi.com
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi, can you send me the picture of where in issue dimensions you want me to add the code you sent, I didnt understand. I dont know what you changed under measures columns or issue rows. I see project under drill into or expand in issues but I dont know how to use this. I know I need to create a user defined field with your code in it but I dont know where and which section.
Capture.PNG
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.