locked
How to calculate pivot-like subtotals using DAX RRS feed

  • Question

  • Hi all,

    I am new to PowerPivot and think it is a very nice tool. Only the ability to avoind having to use VLOOKUPs by connecting different tables made installing the add-on really worth while.

    I do however have one question about something that I am not able to figure out:

    I have tree tables:

    • mapping
    • employees
    • time written by employees

    The 'mapping' table contains an indicator if a time code is "D" (direct) or "I" (indirect), the 'employees' table contains personnel numbers and the 'time' table contains multiple records per personnel number with different time codes and number of hours written to the specific code.

    What I would like to do is create a calculated column with the total number of direct hours per person. I can easily do that by using Excel to create a pivot table with the sum of hours per personell number filtered by "D", but I do not know how to get that data back into my PowerPivot table. I need the number of total direct hours for further calculations, which is the reason I would like to have it in my table.

    My question is:

    Is it possible to create a DAX function to calculate the total direct hours per employee, using the data I described above?

    Cheers,

    Kirvis

    Monday, March 5, 2012 9:21 AM

Answers

  • Ah. got it.

    That is pretty easy to do. In addition to the calculation already mentioned on my last post, add this new one on the 'personnel' table as a calculated column:

    =CALCULATE( SUM( time[Direct Hours] ) )

    The result is the one shown below.  It takes the [direct hours] on the time table and aggregates them to the proper personnel ID based on the current filter context.

    Image and video hosting by TinyPic



    Javier Guillen
    http://javierguillen.wordpress.com/

    • Marked as answer by Challen Fu Wednesday, March 14, 2012 9:31 AM
    Tuesday, March 6, 2012 6:47 PM
    Answerer

All replies

  • Hi Kirvis,

    do you *need* to have the mapping table?  Or could your employee table include another column with the time code?  In other words, is the relationship between the mapping table and the employee table a one to one relatonship?

    Can you post a small sample of what your data looks like?




    Javier Guillen
    http://javierguillen.wordpress.com/

    Monday, March 5, 2012 10:56 AM
    Answerer
  • Hi Javier,

    Thanks for your answer, I will try to explain a bit more of what I want to do.

    Yes, I do need the mapping table. It contains more than just the direct/indirect indicator. In total, I have 200+ different time codes, which are all mapped to 20 main processes. The mapping table has a one to many relation with the time table for the "time code" field.

    The time table can contain multiple time codes per personnel ID, which results in the fact that the personnel table has a one to many relation with the time table for the "personnel ID" field.

    I have just sent you an example flie that should clarify things a bit. I would like the values on the Excel tab "pivot direct hours" to be calculated using a DAX function rather than using a pivot table in Excel.

    Hope this makes it a bit more clear.

    Cheers!

    Monday, March 5, 2012 12:08 PM
  • Hi Bastiaan

    I got the file.   If what you need is only to compute the hours for direct employees, You can add a calculated column to the 'Time' table with the following DAX expression:

    IF(RELATED(mapping[direct or indirect]) = "D", time[hours])

    What this does is that for each row, it will check if the employee is in a direct or indirect capacity (based on time code) and if its direct it will retrieve the hours.  On the pivot table, only direct hours will considered when executing a SUM aggregate on that column.  Will that work for you?




    Javier Guillen
    http://javierguillen.wordpress.com/


    Tuesday, March 6, 2012 12:28 AM
    Answerer
  • Hi Javier,

    Thanks for your reply. Your solution almost does the trick, however, it does not sum the direct hours yet. In the personnel table, I would like a column with on each row, the sum of the direct (D) hours for each employee.

    The reason I created a pivot table in the example file is to show you the values I need in that new column. What I am looking for is a DAX formula to create that colum with subtotals in the personnel table.

    Hope I have made my issue a bit more clear now.

    Cheers!

    Tuesday, March 6, 2012 7:57 AM
  • Ah. got it.

    That is pretty easy to do. In addition to the calculation already mentioned on my last post, add this new one on the 'personnel' table as a calculated column:

    =CALCULATE( SUM( time[Direct Hours] ) )

    The result is the one shown below.  It takes the [direct hours] on the time table and aggregates them to the proper personnel ID based on the current filter context.

    Image and video hosting by TinyPic



    Javier Guillen
    http://javierguillen.wordpress.com/

    • Marked as answer by Challen Fu Wednesday, March 14, 2012 9:31 AM
    Tuesday, March 6, 2012 6:47 PM
    Answerer
  • Thanks! That is exactly what I was looking for. 

    Wednesday, March 14, 2012 5:09 PM