CODEWITHSUNDEEP
excelpivot-tablepowerpivot
MPO
MPO

Reputation: 11

Summarize data in Excel as a matrix

I have a simple table in Excel like the following one:

source data

I need to transform it so that it looks like this:

summary matrix

How can I accomplish this in Excel using pivot table or power pivot (macros excluded)?

Thanks!

Update: In my specific case, the pivot does not need to show grand totals. However there may be other situations where they are.

Upvotes: 0

Views: 2638

Answers (2)

MPO
MPO

Reputation: 11

Here is the best solution I found so far starting from the suggestions I received:

  1. Make sure to check the "Add this data to the Data Model" in order to be able to create a new Measure. create pivot table with the option "Add this data to the Data Model" checked

  2. Create the new Measure and use the following function: Create new Measure with CONCATENATEX function

    =CONCATENATEX(Table1;Table1[Result] & " with " & Table1[Grade];", ")

Remarks: The function CONCATENATEX offers the advantage over the funtion VALUES that the pivot table can have grand totals on rows and columns, which by default are enabled when you create a brand new pivot.

Upvotes: 1

Alexis Olson
Alexis Olson

Reputation: 40244

Create a pivot table with Student on row and Exam on columns and add a measure that concatenates Result and Grade.

TextMeasure = VALUES(TableName[Result]) & " with " & VALUES(TableName[Grade])

Note that this will break for subtotals and grand totals. However, there are ways to fix that.

One way is to do a single value check (and return blank otherwise):

TextMeasure =
IF (
    HASONEVALUE(TableName[Result]) && HASONEVALUE (TableName[Grade]),
    VALUES(TableName[Result]) & " with " & VALUES(TableName[Grade])
)

A shorter way is to just use MAX instead of VALUES but this won't have blanks for totals:

TextMeasure = MAX(TableName[Result]) & " with " & MAX(TableName[Grade])

Upvotes: 0

Related Questions