How to do calculated values in Excel Pivot Table

Question

I have a table like this:

Year       Num  Freq.   Exam    Grade   Course
2014    102846  SM              SM      Astronomy 3
2015    102846  12,6    1,7     NC      Astronomy 2
2017    102846  20      11,8    17      Astronomy 2
2015    102846  SM              NC      Defence Against the Dark Arts 4
2015    102846  11      4,5     NC      Herbology 2
2015    102846  15      13,99   14      Herbology 2

I am trying to get the percentage of approved students (Grade >= 10) for each course by year and global average. I've been trying for nearly 3 hours to do a calculated field but so far the only thing I could get was the sum of each student per year:

I have tried to do a calculated field with = Grade >= 10 hoping that it would give me a list of approved students but it gives me 1.

What am I doing wrong in here? It's my first time working with pivot tables.

Does field Grade contains only numeric values? Because in the data you posted as example, it does not — Foxfire And Burns And Burns
No it can contain letters or even be empty. In both of these it means he is not approved — XXSD

Wizhi Wizhi · Accepted Answer · 2021-05-23T12:39:45

I would really recommend to not mix string type (text) together with numbers. It's a horrifying idea and will cause a lot of headache when data will be used for calculations (both Freq. and Grade). Rather I would use 0 or some numeric value to represent the text.

Not recommended, but yes it's doable =)

You need some dummy variable to point out which row is number and which is text. So I created Grade Type. We can now count only the rows that have a number in the Grade column by using Grade Type = Number.

I create a table of the data and add the column Grade Type. I use this formula to get Grade Type:

=IF(ISNUMBER([@Grade]),"Number","Text")

I then create the following measures:

Nr of Approved Students

=COUNTX(FILTER(Table1, Table1[Grade Type]="Number"), 
IF((VALUE(Table1[Grade])>=10),VALUE(Table1[Grade]),BLANK()))

First we filter which rows that should be evaluated (COUNTX(<table>,...)). If yes, then only count for rows that fulfill >=10, where VALUE() converts string number to numeric (COUNTX(...,<expression>)).

Nr of Student (w/ Grade Number)

=COUNTX(FILTER(Table1, Table1[Grade Type]="Number"), VALUE(Table1[Grade]))

Count all rows that have a number

Approved (% of Total)

=[Nr of Approved Students]/[Count of Grade]

Setup the PowerPivot Table

Create the PowerPivot and add the data to the data Model

Then create a new measure by clicking your pivot table and then "Measures" -> "New Measure..." Fill in all the relevant data.

Result should be something like:

How to do calculated values in Excel Pivot Table

1 Answers