none
DAX Formula for Percent Of Total that will work on Employee's AND the grand total. RRS feed

  • Question

  • Hi all,

    I am working on a DAX problem that someone may be able to help me with.  I have a Total Hours measure:

    Total Hours:=SUM(Timesheet[Hours])


    And a PCT Of Total Hours measure:

    PCT Of Total Hours :=
    IF (
        ISFILTERED ( Timesheet[EID] ),
        DIVIDE (
            [Total Hours],
            CALCULATE ( [Total Hours], ALLSELECTED (), VALUES ( Timesheet[EID] ) )
        ),
        BLANK ()
    )
    

    The desired outcome is for each employee's "PCT of Total Hours" to be the percentage for his/her hours as filtered with the current Slicer (A date slicer, and an Employee Name slicer).

    In the above screen shot, you see the tail end of the pivot report.  The PCT Of Total Hours measure is working fine, but it's giving me a blank on the grand total.  I have tried removing the ", BLANK() at the end of the measure, and I still get blank grand totals.   I suspect it may have something to do with the "ISFILTERED" as the grand total is still filtered to a date window and a subset of employees. 

    If I remove the ISFILTERED clause, as shown here:

    PCT Of Total Hours :=
    DIVIDE (
        [Total Hours],
        CALCULATE ( [Total Hours], ALLSELECTED (), VALUES ( Timesheet[EID] ) )
    )
    

    The totals I get are not correct as shown here:

    There are only two tables in the data model.  Timesheet and dimCalendar.

    Any assistance would be appreciated.

    Thanks,

    John


    John Thomas

    Tuesday, January 14, 2020 8:39 PM

All replies

  • Monday, January 20, 2020 3:20 AM
  • Herbert,

    Thanks for taking a stab at this, but you didn't incorporate the full complexity of the situation as outlined above.

    1. No Date Table in your solution.  Employees work hours every day of the week.  Some on weekends.  I need the hours totaled by our Week End date (Sundays) for this particular report, but we have other reporting requirements which require reporting by Period (13 periods / year), Quarter, Month, etc. so a date table is used to slice the timesheet data as needed.
    2. Looks like your hours are Time?  02:30 for example.  We are not talking about time here, this is hours worked per day totaled to 80 hours in a two week period as shown in my screen shots.
    3. You used the old pivot table's ability to show a value as the percent of the grand total.  while this works in this situation, I need a DAX formula that will work on multiple time slices (Weeks, Period, Quarters, etc.).

    If you can help with the original problem as outline I would appreciate it.

    Thanks,

    John


    John Thomas

    Friday, January 24, 2020 8:24 PM
  • Pass.
    Unless you settle on a single requirement
    and provide just the necessary and sufficient data,
    in shared file format, to illustrate the situation.
    Cull data from your business database or wing it.

    Saturday, January 25, 2020 12:24 AM
  • It would help if you would read the post before jumping to a solution.  Everything you need is there. Anyone can do a pivot table.  I need a PowerPivot DAX Solution, which is what I asked for assistance on.   I even provided DAX formulas, tables, and fields.

    John Thomas


    • Edited by jbt_PwrPvt Sunday, January 26, 2020 10:29 PM rewording
    Sunday, January 26, 2020 10:06 PM