Missing value for divisor in formula

Hi all,


I work in a consultancy firm and for us a key metric is billable utilisation (amount of billable hours in a period / available working hours in a period).


I have three data sets which get connected and transformed through an ETL:

- Input 1. Time report - which includes all hours reported by our consultants (identified by a consultant ID) classified by action codes

- Input 2. Action code lookup - which ties each action code to a time category (PTO, BILLABLE, PROF_DEV, etc.)

- Input 3. Consultants - which contains key details for our consultants (including consultant ID), utilisation % target per month and available working hours per month.


The ETL provides in turn three outputs:

- Output 1. A combined new data set containing all of the inputs linked to each other.

- Output 2. A data set that groups and summarises hours by month, consultant and category, including a column for their available hours per month and their target %.

- Output 3. A data set that filters data set 2 by category="BILLABLE" - I have also done some calculations in the same ETL that calculates utilisation by dividing amount of hours (which are filtered by billable) by the available working hours.


The issue is that for months where a consultant may not have done any billable hour, I I can't get his/her utilisation to work as there is no value for the divisor.  Since there would be no hours reported to a code that can be categtorised as BILLABLE, there will be no entries for them at all in Output 3, and no entry under the category "BILLABLE" in Output 3.


I have read the theads below and can't figure out a solution:




Can anyone help?


Thank you!




Best Answer

  • AS
    AS 🔵
    Answer ✓

    Hi Oliver


    We have a situation at our company where we have sales goals for products, but sometimes no actual sales are made (per month, or per rep, or per whatever), and we still want to know.  So in a dataflow, I create a matrix of all the dimension possibilities and fill in the gaps with the actual data where it exists.  Then the null or zero values will actually be in the data, instead of just missing entirely.  That makes card interactions and beast modes more usable.

    Does that make sense?  Let me know if you need more details.





    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"


  • Thank you, Aaron! That should do the trick. I'll try to do that and will come back if I need extra help.