Search code examples
ssascube

(SSAS) Need to use the total of a calculated measure in other calculations irrespective of - whether they qualify or not

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>
Solution

  • 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;