In this video, I'll briefly cover a few potential examples for how you can use relationships with data
sets and power pivot to quickly garner some useful tidbits of information.
I have a brief overview as to what kind of property is a problem.
Needs to make it friendly for power pivot, plus a pair of scenarios that I'll review and how I generate
a solution for them.
Let's start with a checklist.
Does your problem involve multiple data sets that need to be linked together and some type of aggregation
of results for informational purposes?
Is that the entire list?
Yes, it's a very short checklist, but encompasses a huge variety of different challenges that you
might encounter with your data in our next chapters.
When we get into more complex manipulation of the data model and pick up some DACs will add some additional
special scenarios to this list that are somewhat related to what we've done, but require additional
tools to solve our course.
Example so far looked at a summary of costs and sales by store and date, and it's very obvious, yet
straightforward example for how to leverage these relationships to link and aggregate information.
But what are some less obvious solutions?
For example, here's a series of invitations that sent out and a list of RSVP that have been received.
We have a common field point them across the name field and we'd really like to know how many yeses,
how many no's and how many no responses we have as an additional benefit.
It might be nice to drill down and figure out who has responded in each way.
And I may have given a secret away by saying drill down.
So let's go build this thing.
Conveniently, our data is already in tables so I can go straight to the relationship manager.
While I do not have a standard dimensional table, I can assume that both my invitations and my responses
are uniquely named.
The accounts that I want to determine will be based upon my invitations, as I will want information
about who has responded to what.
This means that I'll specify my invitations in the top table with names column and then responses as
the related lookup table with its name column as well.
Once this relationship is complete, let's go build a pivot table.
I'm going to summarize a lot of information in one place by dragging my yes no from responses to the
row section and my plus one from my responses to my column section.
Then I'm going to take name from my invitations and place it in the value section where it will automatically
assume that I want to count them.
Now, my pivot table looks like this under no plank.
I have 83 entries, those 83 entries have a blank plus one since the plus one column is empty.
If they aren't coming in the next row, I have one hundred and eleven.
Yes.
Responses who are not bringing a plus one and then one hundred and thirty six.
Yes.
Responses from people who are bringing a plus one.
Overall, we can quickly see that two hundred and forty seven responses say that yes, they will attend.
Finally, the fourth row shows blanks for both entries with a total of one hundred and three.
These one hundred and three people are those who have not yet responded.
In just a few seconds, we've created a table that tracks the current tally of responses.
And better yet, do you want to be able to figure out who hasn't responded?
Well, double click on that one or three and voila, you have a list of people and addresses who haven't
responded yet, so you can send out your next mass mailer.
That's one quick example for using relationships between data to quickly gain some insights and utilize
it with a very straightforward and classic relationship, much like the one we used for our sales data.
Sometimes, however, a relationship might not be quite so clear.
You may be asking yourself, why are these colors randomly appearing on my screen?
You might also ask, why is there a number associated with each one of them?
Well, that's the premise of the next example.
The order seems random.
However, I'm not entirely sure that it is wink wink.
In fact, I think the next color in the wheel depends upon the previous color in the wheel.
It may not seem obvious, but this is another opportunity to use relationships.
The traditional Excel approach to this would be to write a formula or simply copy and paste everything
off by one row in either direction.
However, that both violates the premise of this course, and b really isn't the best way to do this
type of computation, especially if you might need to test different levels of offset, such as one
row, two rows, three rows or more.
Now, to make a truly flexible solution here, you need both power query and the power pivot.
Here's what my PowerCore Apeace looks like.
You could just as easily do this by copying and pasting the existing table, adding one to every row,
deleting the last row and adding the resulting table to power pivot as well.
In any case, our data model now has two versions of nearly the same table, except one has the turn
in which the color curtain and then the other table has the turn that we think that the color impacts.
So row one in our first table is one in red, which is what actually happened in turn one.
Row one in our second table is two and red, which says that we think the color red somehow impacts
what the color in turn to is.
This gives us a relationship we can utilize on our turn and next turn column, along with two different
color fields to evaluate permutations in my relationships editor, I'll create a relationship between
our color by next turn with its next turn column and the color by turn and its turn column pressing.
OK, I'll now insert a pivot table that uses our data model and note that we have two tables.
I'll put the starting color for our next turntable in the row section, I'll put our color in the columns
section and I'll drag our next turn to the value section and change it to account.
Upon doing this, I now have a two dimensional table that shows me where the color started on the left
and where it ended up in at the next turn along the top and then in the each cell it will tell you that
number of times that this occurred.
So in this scenario, we can see that when it lands on yellow, it tends to stay on yellow for a long
time as yellow is the starting color and the ending color 80 times.
And then it only ever goes to green.
Otherwise, similarly, blue never follows blue and green and red generate a fairly random response.
Based on this and the fact that I built the random algorithm that generated these colors, this seems
to provide some meaningful insight into our color patterns, perhaps it could be used to predict colors
into the future if there were some need to do that anyhow.
With that will conclude this chapter.
I introduced you to Relationships and Excels Power Pivot, along with a brief overview of some simple
data theory.
This lesson showed you two alternative uses for the pivot table relationships outside of the simple
aggregation example I used in the prior lessons.
In our next chapter, I'll introduce you to some more detailed aspects of exile's data model, along
with how to write pivot table formulas using DACS and will really begin to take advantage of power pivot's
infrastructure.