Search code examples
pivot-tablepowerquerypowerpivotslicersexcel-2019

Link Excel pivot table to slicer with many-to-many relationship with PowerPivot or PowerQuery

I'm struggling to figure out the correct Excel data model relationship technique to link a slicer to a pivot table in a two-step process.

Here is my simple Excel file to download.

There are three fields in my example data: Person, Activity, and Cost. I want a way for users to pick an Activity (or multiple Activities) in the Activity slicer, and then show the full Cost and Activity data for any Person who does that Activity. Here is a screenshot of the data table (1.), and its full pivot table (2.). The output I desire is mocked up in (3.). It's basically a subset of (2.). The complication is that I want to show the full record (ie all Activities) for the Person, not just the Activity selected by the user in the slicer. So there's a relationship from Slicer Activity -> Person -> All Activities per person/

Screenshot of data and desired outcome

I can can easily break the process down into some simple joins and relationships, but I'm unsure how to implement the below in Power Pivot or Power Query. It must update the final table (Step 4) whenever the slicer is updated. I'm just not sure how to bridge from the Activity slicer to the Person table, and then from the Person table back to the source Data.

screenshot of data model stages

Can this be done simply in Power Pivot?

Solution

  • You are going to need to use the Data Model in Excel, with a Measure, to achieve this. Here is an overview of the steps, then followed by the detail.

    1. Load your data table into PowerQuery, then we'll duplicate it to have a new table of just the distinct Activity values. We will use the new table for the slicer only.
    2. From PowerQuery, load the data into the Data Model (only). We won't be using any relationships. We will create one measure to return the cost

    Here are the steps:

    1. Load table into PowerQuery

    • Select your table with table headers and then Data tab (in the ribbon) > From Table/Range (left side)
      enter image description here
    • Power Query Editor will open up with your table. Left side, right-click on Table 1 and rename it to Activity.
    • Right-click on Activity (again on the left) and select Reference. This will create a new query (table) based on the first.
    • Right-click on this new table and rename it to Activity Slicer.
    • In Activity Slicer, right-click on the Activity column header and select Remove other columns.
    • Right-click again on the same column header and select Remove Duplicates.
    • At this stage, you should have two Queries (tables), 1. Activity (the same as in the spreadsheet); 2. Activity Slicer - single column Activity with distinct values.
    • Home tab (in the ribbon) select the down arrow on Close & Load and select Close & Load To... enter image description here
    • In the Import Data window, select Only Create Connection and at the bottom select (check) Add this data to the Data Model.
      enter image description here

    2. Create measure in the Data Model

    • Back in main Excel, go to the Power Pivot tab and select Manage (first one, with Data Model underneath it).
    • Power Pivot for Excel window will open, and in here you will see a tab at the bottom of each query (table) from step 1 above.
    • Select the Activity tab, and then select any cell in the Calculation Area. If Calculation Area isn't visible, then select it in the ribbon.
      enter image description here
    • Enter the following into the formula bar:
    Total Cost:=
  var persons = CALCULATETABLE(
    VALUES(Activity[Person]),
    ALL('Activity'),
    TREATAS( VALUES('Activity Slicer'[Activity]), 'Activity'[Activity] )
  )
  var result = CALCULATE(
    SUM('Activity'[Cost £k]),
    FILTER(Activity,  'Activity'[Person] IN persons  )
  )
  
  RETURN result

    That's it - no relationships needed. Close the Power Pivot window and return to main Excel.

    3. Create/add Pivot and Slicer

    • In the Insert tab in the ribbon, click on the down arrow on PivotTable (first one) and select From Data Model.
      • Add Activity > Person to Rows
      • Add Activity > Activity to Columns
      • Add Activity > Total Cost to Values
    • In the Insert tab in the ribbon, and select Insert Slicer. Select the All tab and you should see the tables from the Data Model. Select Activity Slicer > Activity

    That's it - test!

    enter image description here