cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Brianoreilly Regular Visitor
Regular Visitor

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. 

 

Utilisation 22.JPG

 

4 REPLIES 4
rafaelmpsantos Established Member
Established Member

Re: Calculated Measure: Calculate at lower granularity

Show us the measure used to calculate the 3 collumns.

Brianoreilly Regular Visitor
Regular Visitor

Re: Calculated Measure: Calculate at lower granularity

 

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

 

Brianoreilly Regular Visitor
Regular Visitor

Re: Calculated Measure: Calculate at lower granularity

@Greg_Deckler, would you have any idea?

 

Thanks, 

Brian. 

 

 

Community Support Team
Community Support Team

Re: Calculated Measure: Calculate at lower granularity

Hi @Brianoreilly

I make a test with the following data

a table like below

7.png

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, 

6.png

 

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 6 members 916 guests
Please welcome our newest community members: