-1

I have a dimension which categorizes labor as direct and indirect now I have a measure [indirect hours] which comes only for indirect employees and others remain blank. and another measure [direct hours] which comes only for direct employees and others remain blank.

I want to create a calculated measure [indirect hours]/[direct hours] and show it in front of all direct employees.

Currently it is showing all blank there. Any help would be much appreciated

Edit:

 LaborType  |   DirectHrs   |   IndirectHrs | Calculation_Expected 
 direct         10              <Blank>         (1+2+3+4)/10=1 
 direct         20              <Blank>         (1+2+3+4)/20=0.5 
 direct         30              <Blank>         (1+2+3+4)/30=0.33 
 direct         40              <Blank>         (1+2+3+4)/40=0.25 
 Indirect       <Blank>             1           <Blank> 
 Indirect       <Blank>             2           <Blank> 
 Indirect       <Blank>             3           <Blank> 
 Indirect       <Blank>             4           <Blank>
Wyatt Shipman
  • 1,669
  • 1
  • 10
  • 22
Arun Saxena
  • 3
  • 1
  • What have you tried? Can you give us an example of the data your are expecting to see? So what are the values for Indirect Hours, Direct hours, and your calculation? – Wyatt Shipman Mar 17 '16 at 15:37
  • my calculation is to divide the indirect hours by direct hours -for ex : LaborType | DirectHrs | IndirectHrs | Calculation_Expected ------------------------------------------------------------------------- direct 10 Blank (1+2+3+4)/10=1 direct 20 Blank (1+2+3+4)/20=0.5 direct 30 Blank (1+2+3+4)/30=0.33 direct 40 (1+2+3+4)/40=0.25 Indirect 1 Indirect 2 Indirect 3 Indirect 4 – Arun Saxena Mar 18 '16 at 09:32
  • Are the indirect hours supposed to be in direct hours column or indirect hours column? – Wyatt Shipman Mar 18 '16 at 14:11

1 Answers1

0

If the table I edited and put into your post is correct, you could use a calculation like below:

CREATE MEMBER CURRENTCUBE.[Measures].[Calculated Member]
 AS CASE WHEN IsEmpty([Measures].[DirectHrs])
THEN NULL
ELSE ([Measures].[IndirectHrs],[DimensionName].[LaborType].[Indirect])
/
[Measures].[DirectHrs]
END;
Wyatt Shipman
  • 1,669
  • 1
  • 10
  • 22
  • Thanks for your help Wyatt however I am getting the syntax error in ALL Executing the query ... Query (4, 76) Parser: The syntax for 'All' is incorrect. Execution complete – Arun Saxena Mar 22 '16 at 12:35