Total of Measure in aggregated structure

Questions & Answers
1

Hello,
I have a structure of issues nested by link (“contains in”)

I need a Measure/Total that can display sum of columns (leaf1_todo, leaf1_in progress) like it sums in [Measures].[Issues created]
image

like this
image

3

I’ve found:

  • Total value for leaf1 issues
  • Displayed it in root, leaf0 rows

image

Still looking how to:

  • display sums just for current root

like here
image

4

I’ve found solution

image

image

image

NonZero(
        Count(
                Filter(
                        --get desc of root hierarhy('INIT')
                        Descendants([Issue].CurrentHierarchyMember.GetLinkedMember('INIT')),
                        IIf(
                                --filter by issue type
                                IsEmpty(ExtractString([Measures].[Issue type], 'CTE'))
                                ,
                                0,
                                iif(
                                        --filter by status
                                        IsEmpty(ExtractString([Measures].[cat0], 'To Do'))
                                        ,
                                        CBool(0),
                                        CBool(1)
                                )
                        )
                )
        )
)

I would be glad if you can tell less complicate solution than mine or point to mistakes

1 Like
5

Hi @cybertachikoma
Try creating a calculated measure using this formula:

(
[Measures].[Issues created],
[Issue type].[CTE],
[Status].[To Do]
)

It should look for specific issue type and status in the hierarchy against each row in the report.

Martins / eazyBI

6

Hi, Thank you for answer.

It’s not working in my case.

image
image707×269 10.9 KB

image

7

I’ve tried all variants linked with my hierarchy “INIT”

image

Same result - no error, no data

image
image774×289 12.3 KB

8

What formula do you use to calculate “leaf1_todo” measure right now?
And can you add “Issue status” to columns and share another screenshot?

Martins /eazyBI

9

Hi, I’m sorry for late answer.

- What formula do you use to calculate “leaf1_todo” measure right now?

this is for “leaf1_todo”

CASE
WHEN
IIf(
        IsEmpty(ExtractString([Measures].[Issue type],  'CTE'))
        ,
        0,
        iif(
                IsEmpty(ExtractString([Measures].[status_category],  'To Do'))
                ,
                CBool(0),
                CBool(1)
        )
)
THEN 1
END

and this for “status_category”

[Status.Category].[Status].getMemberByKey(
  [Issue].CurrentHierarchyMember.get('Status ID')
).Parent.Name

- And can you add “Issue status” to columns and share another screenshot?

image
image911×281 15.5 KB

10

And additional question:
When collapsed - it sums Total

image
image864×149 7.88 KB

But when Expanded - no Total.

image
image906×288 15.7 KB

How to Sum in any case(expanded/collapsed)?

11

I’ve created discreete question for that

12

For custom formulas in calculated measures you can try using special comments with annotation in the first line of your calculated measure formula.

-- annotations.total=sum

When added as first line to your formula in the calculated measure, it would force to show the sum correctly in report.

Martins / eazyBI

13

Hello,

There’re two questions in this Topic, let’s discuss the last one (about -- annotations.total=sum) in
new Topic?

//Total of Descendants in Collapsed/Expanded view

unfortunately, that’s not working. I’ve decribed It in
//Total of Descendants in Collapsed/Expanded view