Getting Started with Calculated Fields

The first two blog posts of this series focused on getting the data in from Yammer’s Data Export into Power Pivot and then how to set up a Date Table to better manipulate the information. This set the foundation for getting data into Power Pivot. Now we’ll get into the fun stuff, calculated fields. Let’s get started:

Let me count the ways…

One of the best ways to use a calculated field is to get a total of certain things, like how many users are in network, how many posts, etc. In this example, we’ll work with Groups table and figure out how many groups we have in our network.

counts

Get a Group Count:

  1. In the Groups table, click into one of the cells below the last visible date point. The area here is where we’ll be entering our various calculated fields.calculated fields
  2. In the formula editor, enter the following:
    • Group Count:=COUNTROWS(Groups)
    • We just used the COUNTROWS function. This is counting the number of rows in the table and returning the total number it finds.

This is great, but as you can see from the table, there are groups that have been deleted. Let’s take a look at how we can count a specific subset of rows from the table.

Count just the Active Groups:

  1. Click into the cell below Group Count.
  2. In the formula editor, enter the following:
    • Active Groups:=COUNTROWS(FILTER(Groups, Groups[deleted]="false"))
    • We’ve now included the FILTER function. The function filters a specific table (Groups) on a specific criteria (where the deleted column is false). The COUNTROWS function then comes in and counts up the number of rows in that subset.

That was fun, but let’s get a little crazy. What if we want to get a count of the number of public groups in our network? How do you think you do this? Before you scroll down and see how, try it yourself!

Count the number of Public Groups:

  1. Click into the cell below Active Groups.
  2. In the formula editor, enter the following:
    • Public Groups Count:=COUNTROWS(FILTER(Groups, Groups[private]="false" && Groups[deleted]="false"))
    • Looking at the Groups table, you probably noticed the private column. If you challenged yourself before looking at the answer, you probably just set Groups[private]=”false” and called it a day. But remember, we also have deleted groups in this table, so we want to take those into account. We can use && logical operator to create an AND condition between two expressions. If you wanted to create an OR condition, you would use || (double pipe symbol). 

You can use the above methods to get a count of how many users are on your network, how many messages, how many threads, etc. Stay tuned for future blog posts where we’ll take a look at calculated columns and do some advanced calculated fields.

 

Leave a Reply

Your email address will not be published. Required fields are marked *