cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Yggdrasill
Resolver III
Resolver III

Calculate correct total for a ratio

Hello.

 

I've been struggling with something that seemed to be rather easy but somehow it isn't.

 

I have 3 tables. One factTable with summarized hours workers register. I have one employee table (Resource) where I have the extra information on when they started and if they have quit then there's an end date as well.  I also have a calendar table. Relationship can seen on the picture.



model.PNG

 

For the measures it's pretty simple.

 

Hours = SUM('Mandatory Table'[Hours])
Mandatory hours can be found from the Calendar Table and Resource Table

Mandatory =
VAR _mandatoryHours = 7,5 //mandatory hours per day
//WorkRatio from Calendar is either 0 or 1 based on the day of the year
VAR _daycalc =
    CALCULATE (
        SUM ( Calendar[WorkRatio] ) * _mandatoryHours;
        FILTER ( 'Calendar'; Calendar[Date] <= 'Last Refreshed'[LastRefreshed] ) //filter future dates out
    )
        * AVERAGE ( Resource[Work Ratio%] ) //some resource are not 100% full time
VAR _calc = _daycalc * [Employee Count] //distinct count of resources
RETURN
        IF ( ISINSCOPE ( 'Calendar'[Date].[Year] ); _daycalc; _calc ) 

 Now for the ratio

Mandatory% =
VAR _calc1 =
    DIVIDE ( [Hours to Registered Service Lines]; [Mandatory]; 0 )
RETURN
    IF ( [Mandatory] = 0; BLANK (); _calc1 )

 This is what I get in return when I look at one resource and it seems to work

 

table1.PNG

 

Now when I look at a resource that did quit (on the 17th) I get a wrong total

 

table3.PNG

 

My question is, How can I leverage the result with DAX so that I can get a correct Total ? For this instance the mandatory hours should be 0 because the resource is no longer working.

 

I somehow need to check with the end date from the resource table and put that into account when I calculate mandatory hours.

 

Thanks in advance ! 

Ps. 'Mandatory Table' is a summarised factTable where I use DAX to summarize it. I'd like to be able to skip the physical creation of another table and just do the calculations within the vertipaq engine if that's possible.

5 REPLIES 5
SQLbyoBI
Advocate I
Advocate I

are we to assume these calculations will only ever be used in the context of a single employee, or should the calculation be able to handle scenarios where there are multiple employees in context?

 

If we assume a single employee, then this should help...

Mandatory =
VAR _mandatoryHours = 7.5 //mandatory hours per day

VAR __cur_resource_last_date = SELECTEDVALUE( Resource[EndDate] )

//WorkRatio from Calendar is either 0 or 1 based on the day of the year
VAR _daycalc =
    CALCULATE(
        SUM( Calendar[WorkRatio] ) * _mandatoryHours,
        FILTER(
            'Calendar',
            AND(
                'Calendar'[Date] <= 'Last Refreshed'[LastRefreshed],
                'Calendar'[Date] <= __cur_resource_last_date
            )
        )
    )
        * AVERAGE( Resource[Work Ratio%] ) //some resource are not 100% full time
VAR _calc = _daycalc * [Employee Count] //distinct count of resources
RETURN
    IF(
        ISINSCOPE( 'Calendar'[Date].[Year] ),
        _daycalc,
        _calc
    )
EVALUATE
 ( 'calendar' )

 

Thank you for the review!

However I have to be able to see one or multiple employees which are stores in the resource table.


@Yggdrasill wrote:
Thank you for the review!

However I have to be able to see one or multiple employees which are stores in the resource table.


in that case, you can probably get away with something like this...

 

Mandatory STAGE =
VAR _mandatoryHours = 7.5 //mandatory hours per day

VAR __cur_resource_last_date = SELECTEDVALUE( Resource[EndDate] )

//WorkRatio from Calendar is either 0 or 1 based on the day of the year
VAR _daycalc =
    CALCULATE(
        SUM( Calendar[WorkRatio] ) * _mandatoryHours,
        FILTER(
            'Calendar',
            AND(
                'Calendar'[Date] <= 'Last Refreshed'[LastRefreshed],
                'Calendar'[Date] <= __cur_resource_last_date
            )
        )
    )
        * AVERAGE( Resource[Work Ratio%] ) //some resource are not 100% full time
VAR _calc = _daycalc * [Employee Count] //distinct count of resources
RETURN
    IF(
        ISINSCOPE( 'Calendar'[Date].[Year] ),
        _daycalc,
        _calc
    )
EVALUATE
 ( 'calendar' )

...then create another measure that references the one above at the resource level...

 

 Mandatory =  
    SUMX(
        /* or whatever the grain of the resource is called */
        VALUES( 'Resource'[ResourceID] ), 
        [Mandatory STAGE]
    )

This is a calculation needs to happen at the resource/day grain... so there's probably a better way to handle.

Thank you for your help. It got me started on this project again and I've solved all parts except for one minor problem.

 

I'll post my solution with .pbix demo file in the coming days for possible future help

Great, glad it helped! Please do circle back around and share your final solution.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors