How to pivot chart 2 data types

Question

I am trying to find a way to capture 2 data types in one pivot table so I can make a single chart.

I have a year's worth of survey data (survey had 5 questions with scores of 1 - 4) and I'd like to capture the average score per question per month AND a count of how many surveys were taken during the month.

I can easily capture the average scores and display that in a cluster chart. The dilemma is that I also need ONE column that counts the number of surveys for that month.

My source data includes a Unique Count column but when I add that to the Values section of the pivot table, it adds a count next to each question (thus, it has 5 columns of counts).

So far, the only way I've been able to do it is by creating 2 pivot tables (one with questions and response averages and one with the count) and then setting up a grid to combine that data into 1 table. This is clumsy and would be difficult (but not impossible) to automate.

I've searched online for the following possibilities but haven't been successful: (1) 2 data types in one pivot table (what I described above); (2) 2 pivot tables in a single chart; (3) Consolidate 2 pivot tables into one.

NOTE: I WANT TO ADD A FILE EXAMPLE BUT I CAN'T SEE HOW. PLEASE ADVISE. Just in case, here is a picture of part of the file.

Answer 1

If you have Excel 2013 or later, you can use the DataModel to do this, regardless of whether you have a version that includes PowerPivot or not. You simply need to add the original table to the DataModel, then write 6 measures (5 of which filter the data by response, and the 6th generates a Grand Total) then put those 6 measures into your PivotTable Values area.

Here's an example, using some sample data I just whipped up:

...and here's the measures that I used to do this:

For a great video showing how to do this, check out Mike Girvin's ExcelIsFun YouTube channel, specifically https://www.youtube.com/watch?v=FVVK-8QZC1M

Another alternative is to use the GETPIVOTDATA function to return the values from the two individual PivotTables you are currently using to the block you are currently feeding the chart.

Lastly, I'd suggest NOT displaying the data like this, but instead breaking it into two charts. For instance, both of these were created using 'traditional' PivotTables:

...and the advantage to the viewer is that it's much easier to follow monthly trends by response type when all similar responses are grouped together.

Answer 2

This is a nice example of using DAX to get what you want from a pivot table, and there are some pivot tables you may need that you have to use DAX for. But you don't even need to use the data model or DAX for this simple case.

Create a pivot table with date in the rows area and each of the survey questions in the values area, and show them as average. These are your survey averages.

Then drag any one of the survey questions (doesn't matter which, and let's assume that each submitted survey has a submitted response for each question) to the values area, and show it as count. This is your survey count column.

Now make your pivot chart. If desired, the Count can go onto the secondary axis.

One advantage to not using a DAX/Data Model pivot table is that you can format the dates in the pivot table using the Field Settings dialog (instead of having to format the underlying range), and the dates along the horizontal axis of the chart will honor this format, or you can format them however you like using the normal Format Axis task pane. The DAX/Data Model pivot chart does not let you format the axis labels.