Forum Discussion

Brianoreilly's avatar
Brianoreilly
Helper II
7 years ago

Calculated Measure: Calculate at lower granularity

Hi Folks, 

 

I have a timecard table with time logged against projects per resource. 

I have a number of measures (need to be measures as I use a date slicer).

 

I need to measures in question to calculate based on the project level first and then roll up to the resource level. 

Some like the opposite of a Calculate All() if that makes sense, where I can state what dimension to calculate on.  

 

 

 

When I include the Project ID in the context it works. 

 

When I remove it calculates at the Resource level which gives incorrect values. 

 

Any help appreciated. 

 

Thanks and Regards,

Brian. 

 

 

Need Help

4 Replies

    • Brianoreilly's avatar
      Brianoreilly
      Helper II
      7 years ago

       

      Slicer Current Period Project Chargeable Utilisaton TC =
      CALCULATE (
    ( [Slicer Current Period Chargeable Days Refined TC]
        / ( [Slicer Current Period Billable Days Refined TC]
        + [Slicer Current Period Non Billable - Other Refined TC]
        + [Slicer Current Period Non Billable - Travel Refined TC] ) ),
    ALL ( 'Timecard Split'[Resource Name] )
)
      Slicer Current Period Days Remaining  Raw TC = 
CALCULATE(IF (
    ( [Slicer Total Sold Days] - [Slicer Before Current Period Billable Days] )
        < 0,
    0,
    ( [Slicer Total Sold Days] - [Slicer Before Current Period Billable Days] )
),All('Timecard Split'[Resource Name]))
      Billable Days Within Budget =
[Days Logged Billable]
    * [Slicer Current Period Project Chargeable Utilisaton TC]

      Hi rafaelmpsantos, see attached. 

       

       

      The measures above work off of other measures, that generally all work like below. 

      Slicer Current Period Billable Days Raw TC = 
CALCULATE(SUMX (
    FILTER (
        'Timecard Split',
        (
            'Timecard Split'[Timecard Start Date] >= MIN ( 'Date'[Date] )
                && 'Timecard Split'[Timecard End Date] <= MAX ( 'Date'[Date] )
        )
            && ( 'Timecard Split'[Billable] = True() && 'Timecard Split'[Project ID] = MAX(Project[ID])
             )
             
    ),
    ROUND('Timecard Split'[Total Hours] / 7.5,10)
),ALL('Timecard Split'[Resource Name]))