Search code examples
excelpivot-tablepowerpivot

Excel Pivot table Data Model measure for displaying a value without calculation

I am confused on how to build a Pivot table relating different Excel tables. The solution might be simple, but I am lost! Say we have 3 tables for dealing with human resources:

  • a table for required activities (each has 2 levels)
  • a table for human resources, people and their working rate (partial time or full time)
  • and a 'junction table' for person_activities where we insert the actual working load of each person on each level.

enter image description here

The model is enter image description here

I want to build a Pivot table like the following one, with a hierarchy person/activity/level (in compact form) for checking that the sum of levels is not over the expected working rate of each people. We want to highlight people whose cumulated activities are over their working rate.

enter image description here

Should I use DAX measures and KPI for dealing with it? (king of SUMX? I don't master DAX formulas alas!). Maybe I am on the wrong direction? Any help welcome!

Solution

  • In order to generate your desired hierarchy, you'll need to first modify table_hr_activity, e.g. via Power Query, unpivoting the level1 and level2 columns, which will give you something like:

    person activity level value
    x a level1 0.3
    x a level2 0.2
    x b level1 0.2
    x b level2 0.5
    y a level1 0.5
    y a level2 0.3

    After you've added this new table to the Data Model, go to the Power Pivot window (relationships view), click on the person field within this table and select 'Create Hierarchy'. Add the activity and level fields from the same table to this hierarchy.

    Then create the following measure:

    WorkingRate :=
VAR ThisPerson =
    MIN( table_hr_activity[person] )
RETURN
    LOOKUPVALUE(
        table_human_resources[working rate],
        table_human_resources[person], ThisPerson
    )

    You can now create a Pivot Table with the hierarchy you created in the Rows section, with Sum of value (from the newly created table) and the above measure in the Values section.

    Edit: to return the Working Rate at only the top level of the hierarchy:

    WorkingRate :=
VAR ThisPerson =
    MIN( table_hr_activity[person] )
RETURN
    IF(
        ISFILTERED( table_hr_activity[activity] ),
        BLANK(),
        LOOKUPVALUE(
            table_human_resources[working rate],
            table_human_resources[person], ThisPerson
        )
    )