Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
db_programmer
Frequent Visitor

Cap Sum by Day

I am not new to DAX but I’m not an expert either.  I need some help, please, developing a calculation and I’m not sure the best approach.

My data model captures data about utilization of resources.  There are many resources and each resource has both available time (denominator) and time that it is used (numerator).   One of the main measures is basically numerator/denominator (utilization rate %):

[Utilization Rate %]:=

VAR Num =

    [Hourly Utilization Adjusted Open-Normal Used Time (minutes)]

VAR Den =

    [Hourly Utilization Adjusted Open-Normal Available Time (minutes)]

VAR NewNum = IF(ISBLANK(Num) && Den > 0,0,Num)

RETURN

    DIVIDE (

        NewNum

        ,Den

        ,BLANK ()

    )

The granularity of the data is user and hour.  That is, if a user makes use of the resource for two hours there are two numerator records.  If two users make use of the resource across two different hours, there are three records:

db_programmer_0-1679321901050.png

 

Sometimes the resource is utilized when it is “not available” and I have a calculation to report on that as well, e.g., [Hourly Utilization Adjusted Closed-Time Used Time (minutes in Business Hours)], and this gets added to the numerator:

 

Hourly Utilization Adjusted Closed-Time Used Time (minutes in Business Hours)]:=

CALCULATE(

    SUMX(

        FILTER(

            'Hourly Utilization Adjusted',

                && ( ( NOT 'Hourly Utilization Adjusted'[Hour of Day] IN { 7, 8, 9, 10, 11, 12, 13, 14, 15, 16 } )

                || ( RELATED( 'Calendar'[Is Holiday 01] ) = 1 )

                || ( RELATED( 'Calendar'[Weekend 01] ) = 1 ) )

        ),

        'Hourly Utilization Adjusted'[Duration]

    )

)

[Utilization Rate Adjusted %]:=

VAR Num =

    [Hourly Utilization Adjusted Open-Normal Used Time (minutes)]

    + [Hourly Utilization Adjusted Closed-Time Used Time (minutes in Business Hours)]

VAR Den =

    [Hourly Utilization Adjusted Open-Normal Available Time (minutes)]

VAR NewNum = IF(ISBLANK(Num) && Den > 0,0,Num)

RETURN

    DIVIDE (

        NewNum

        ,Den

        ,BLANK ()

    )

 

  In other words, treat the time as if it were available even though it was not.  Specifically, if the resource is utilized during normal business hours, even though a resource is marked “not available”, we count the utilization as if it were available.  That part works perfectly.

 

db_programmer_1-1679321946460.png

The problem I am running into is we have some extended hours where the business would also like to count the unavailable resources but based on parameters.  Specifically, there are four (4) resources that ARE available during extended hours at the end of the day, hours 17 and 18, resulting in 480 minutes of available time between 5pm and 7pm.   For the other resources that are NOT available during those extended hours, those minutes of utilization should also count as long as they aggregately do not exceed a combined 480 minutes per day.  Basically, unlike normal business hours where ALL unavailable resources count, during extended hours, unavailable resources count, but only up to a certain threshold. 

That is the part that I think is tricky.  If the pivot grid is sliced by date only, I could just filter my calculation based on the hour of the day and add up the closed time to see if it exceeds 480 minutes, and use an if statement to cap it.  The problem is if the pivot grid is sliced by hour of day or by resource or anything else, the 480-minute calculation would not work.

For the normal business hours, the calculation that brings in the unavailable numerator works at any granularity (i.e., from hour on up to year or higher):

 

db_programmer_2-1679321987051.png

 

How can I calculate the numerator, capping it off at 480 minute per day and make sure it is accurate at other granularities besides date?

The only approach I have conceptualized might work is to create a calculated table that summarizes the 5p to 7p usage by date, as an intermediate step for the rest of the calculation.  It seems like I need to somehow check the summary level in my calculation to see if the summary is by day or higher.  I’m not sure how to handle robustly the different ways the data could be sliced, e.g., day, week, month, year, etc., plus resource, location (the data has different locations), etc.

Note:

  • Ideally, the 480-minute threshold would come from the data itself based on how many minutes/resources are available between 5pm and 7pm, but I am okay with hard-coding it as well if it simplifies the overall solution. 
  • I’m okay with the calculation not using the cap if the summary level is lower than day.  For example, if summarized by hour, go ahead and give credit for all unavailable utilization, even if it exceeds 480 minutes.  The important calculation is when it is by date or higher.

 

 

1 ACCEPTED SOLUTION

you sample data and the expected outcome don't agree.

 

lbendlin_0-1679493116041.png

Anyway, here's my proposal.  You could hide the extra "cap"  columns or ignore them.

 

View solution in original post

4 REPLIES 4
db_programmer
Frequent Visitor

Here is some sample data:

DateMonth-YearHourResourceDuration Used
20230101January 202316160
20230101January 202316260
20230101January 202316360
20230101January 202316460
20230101January 202316560
20230101January 202317159
20230101January 202317260
20230101January 202317360
20230101January 202317460
20230101January 20231751
20230101January 202318160
20230101January 202318260
20230101January 202318360
20230101January 202318460
20230101January 202318560
20230102January 202316160
20230102January 202316260
20230102January 202316360
20230102January 202316460
20230102January 202316560
20230102January 202317160
20230102January 202317260
20230102January 202317360
20230102January 202317460
20230102January 20231751
20230102January 202318260
20230102January 202318360
20230102January 202318460
20230102January 202318560
20230102January 202316160

 

Here are the expected results:

 

db_programmer_1-1679486825424.png

 

 

you sample data and the expected outcome don't agree.

 

lbendlin_0-1679493116041.png

Anyway, here's my proposal.  You could hide the extra "cap"  columns or ignore them.

 

Beautiful, thank you!  I use an uncommon combo of tools (Tableau, Tabular Editor, and SSAS) - no Power BI...yet.  I'll plug this into TE3 and give it a shot, but your results are spot on.  Sorry about the sample data being off.  Nothing like bad data to throw off the example, but glad you caught it.  

lbendlin
Super User
Super User

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors