Calculated Measure: Calculate at lower granularity

[Brianoreilly]

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. 

[rafaelmpsantos]

Show us the measure used to calculate the 3 collumns.

[Brianoreilly]

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]))

[v-juanli-msft]

Hi @Brianoreilly

I make a test with the following data

a table like below

Create measure1~measure6 use the columns in the table above(thus when add these measure in the visual or use these measures in other measures which add to the viusal, the measure can be affected by row content(each row of "name", "id" column)

then create measure7,8 with the measure1~measure6, 

I haven't reproduce your problem untill now, could you show how you tables scrutures?

Or could you show me your example file for better analysis via private message?

Best Regards

Maggie

[Brianoreilly]

@Greg_Deckler, would you have any idea?

Thanks, 

Brian.