Totalizing a user defined measure

Questions & Answers
,
1

I would like to count on your support for the following issue that I am experiencing:

In the Issues database, I have created a user defined measure sqiIndex. The formula for this measure is:

CASE
WHEN
[Measures].[Issue status] = “Closed”
THEN 0
WHEN
[Measures].[CRITICALITY] > 7
THEN 8
WHEN
[Measures].[CRITICALITY] > 2
THEN 4
ELSE
1
END

sqiIndex depends on other user defined measure CRITICALITY which formula is:

IIf([Measures].[Issue Severity] = “Major”,2,1) *
(CASE
WHEN [Measures].[Issue FREQUENCY] = “Frequent” THEN 3
WHEN [Measures].[Issue FREQUENCY] = “Possible” THEN 2
ELSE 1
END)
IIf([Measures].[Issue Source] = “Customer”,2,1))

Now, I would like to generate a report with the sum of sqiIndex field per Issue Type, as showed in the screenshot included in the attached file screenshot.png.

To do it, I have created a new user defined measure: sqiTotals, which formula is:

NonZero(Sum(
Filter (
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
/* Filter */
(not isempty([Measures].[Issue status]) AND
[Measures].[Issue type] = “Defect” AND
[Measures].[Issue CF Issue Type] = “PRND”
)),
[Measures].[sqiIndex]))

I am unable of getting the right sqiIndex sum per Issue Type when the field used is user defined, while the report works fine when the field is a predefined one (for instance Issues created in the attached screenshot and test9 user defined field that uses the same formula that sqiIndex but referred to a predefined measure, rather a user defined one. This is test9 formula:

NonZero(Sum(
Filter (
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
/* Filter */
(not isempty([Measures].[Issue status]) AND
[Measures].[Issue type] = “Defect” AND
[Measures].[Issue CF Issue Type] = “PRND”
)),
[Measures].[Issues created]))

I would like to point out that the 170 number appearing in every row of sqiTotals column is the total sum for sqiIndex for all Issue Type, I mean, 170 should be the sum of every number in column sqiTotals.
I would appreciated whatever help you can provide to me.

screenshot
screenshot1228×900 35.9 KB

2

Hi @mrd

Your measure where you calculate totals is missing the numerical measure for the context - to create a relationship between other dimensions in report calculation.
Try this formula for “sqiTotals” calculated measrue:

NonZero(Sum(
Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
/* Filter */
(not isempty([Measures].[Issue status]) AND
[Measures].[Issue type] = "Defect" AND
[Measures].[Issue CF Issue Type] = "PRND"
)),
CASE WHEN
[Measures].[Issues created]>0
THEN
[Measures].[sqiIndex]
END
))

I’m using “Issues created” numerical measure to link all the report dimensions for the calculation.
Martins / eazyBi

3

Hi Martins:
Thank you very much! Your suggestion has worked very well and now I get the right figures in my report.