I made a calculated field but it only works when issue is on rows

Questions & Answers
1

Hi Team , any clues on why the field wont add and average like the others?
any help will be greatly appreciated!!
I will add the formulas “See below”
Yield step formula"IIf([Measures].[Step 7 OUT history] = NULL,[Measures].[Step 7 OUT history],[Measures].[Step 7 OUT resolved])/IIf([Measures].[Step 7 IN resolved] = NULL,[Measures].[Step 7 IN history],[Measures].[Step 7 IN resolved])"

TotalYield Formula"
IIf([Measures].[Issue Step 10]=“Inspect/Bag/COFC”,[Measures].[Step 10 OUT resolved],IIf([Measures].[Issue Step 9]=“Inspect/Bag/COFC”,[Measures].[Step 9 OUT resolved],IIf([Measures].[Issue Step 8]=“Inspect/Bag/COFC”,[Measures].[Step 8 OUT resolved],IIf([Measures].[Issue Step 7]=“Inspect/Bag/COFC”,[Measures].[Step 7 OUT resolved],IIf([Measures].[Issue Step 6]=“Inspect/Bag/COFC”,[Measures].[Step 6 OUT resolved],IIf([Measures].[Issue Step 5]=“Inspect/Bag/COFC”,[Measures].[Step 5 OUT resolved],IIf([Measures].[Issue Step 4]=“Inspect/Bag/COFC”,[Measures].[Step 4 OUT resolved],IIf([Measures].[Issue Step 3]=“Inspect/Bag/COFC”,[Measures].[Step 3 OUT resolved],IIf([Measures].[Issue Step 2]=“Inspect/Bag/COFC”,[Measures].[Step 2 OUT resolved],IIf([Measures].[Issue Step 1]=“Inspect/Bag/COFC”,[Measures].[Step 1 OUT resolved],[Measures].[Step 1 IN created]))))))))))/ IIf([Measures].[Step 1 IN created] = NULL,[Measures].[Step 1 IN history] ,[Measures].[Step 1 IN resolved] )
"

image
image1020×272 17.8 KB

2

@mlandaeta

Total yield calculation is created using the properties (for example “Issue step 10” which does not return value on a project or higher levels), therefore your calculation is empty on the project and top level.
You would need to iterate through issues (for the project and total row) to calculate results from issues.
Create a new calculated measure using this formula.

NonZero(

Avg(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
[Measures].[Issues created]>0
),

IIf([Measures].[Issue Step 10]=“Inspect/Bag/COFC”,[Measures].[Step 10 OUT resolved],
  IIf([Measures].[Issue Step 9]=“Inspect/Bag/COFC”,[Measures].[Step 9 OUT resolved],
    IIf([Measures].[Issue Step 8]=“Inspect/Bag/COFC”,[Measures].[Step 8 OUT resolved],
      IIf([Measures].[Issue Step 7]=“Inspect/Bag/COFC”,[Measures].[Step 7 OUT resolved],
        IIf([Measures].[Issue Step 6]=“Inspect/Bag/COFC”,[Measures].[Step 6 OUT resolved],
          IIf([Measures].[Issue Step 5]=“Inspect/Bag/COFC”,[Measures].[Step 5 OUT resolved],
            IIf([Measures].[Issue Step 4]=“Inspect/Bag/COFC”,[Measures].[Step 4 OUT resolved],
              IIf([Measures].[Issue Step 3]=“Inspect/Bag/COFC”,[Measures].[Step 3 OUT resolved],
                IIf([Measures].[Issue Step 2]=“Inspect/Bag/COFC”,[Measures].[Step 2 OUT resolved],
                  IIf([Measures].[Issue Step 1]=“Inspect/Bag/COFC”,[Measures].[Step 1 OUT resolved],
                    [Measures].[Step 1 IN created]))))))))))/ IIf([Measures].[Step 1 IN created] = NULL,
[Measures].[Step 1 IN history] ,[Measures].[Step 1 IN resolved] )
)
)

When selecting it in your report, make sure the “Nonempty” crossjoin is enabled.
it would calculate the average result (from issue-level results) on project level.

Martins / eazyBI