Reporting by "Primary Metrics"

[spudsy]

I'm a long time lurker in this forum (thanks for all the advice over the years!), and I recently finished Rob's awsesome book and feel pretty confident with the basics, but I'm having troubles putting a few pieces together for a new pivot.

I work at a post-secondary institution, and have been tasked with reporting on students enroled in 3+ courses within the Fiscal Year, by both a primary Campus and primary Program, using the following parameters:

[TABLE="class: grid"]
<tbody>[TR]
[TD]Minimum Registration Count
[/TD]
[TD]3+ registrations in the Fiscal Year
[/TD]
[/TR]
[TR]
[TD]Primary Campus
[/TD]
[TD]The student's Campus with the highest registration count in the Fiscal Year (ties favour Central, North, then East)
[/TD]
[/TR]
[TR]
[TD]Primary Program
[/TD]
[TD]The student's Program with the highest registration count in the Fiscal Year (tie-breakers randomly chosen)
[/TD]
[/TR]
</tbody>[/TABLE]

I am using the following testing data set:

[TABLE="class: grid"]
<tbody>[TR]
[TD]Reg No
[/TD]
[TD]Student No
[/TD]
[TD]Fiscal Year
[/TD]
[TD]Term
[/TD]
[TD]Campus
[/TD]
[TD]Program
[/TD]
[TD]Course
[/TD]
[/TR]
[TR]
[TD]10009
[/TD]
[TD]1001
[/TD]
[TD]2009-2010
[/TD]
[TD]2010-Q1
[/TD]
[TD]Central
[/TD]
[TD]Graphic Design
[/TD]
[TD]GRDN-100
[/TD]
[/TR]
[TR]
[TD]10010
[/TD]
[TD]1001
[/TD]
[TD]2009-2010
[/TD]
[TD]2010-Q1
[/TD]
[TD]Central
[/TD]
[TD]Graphic Design
[/TD]
[TD]GRDN-105
[/TD]
[/TR]
[TR]
[TD]10011
[/TD]
[TD]1001
[/TD]
[TD]2010-2011
[/TD]
[TD]2010-Q4
[/TD]
[TD]Central
[/TD]
[TD]Graphic Design
[/TD]
[TD]GRDN-110
[/TD]
[/TR]
[TR]
[TD]10012
[/TD]
[TD]1001
[/TD]
[TD]2010-2011
[/TD]
[TD]2011-Q1
[/TD]
[TD]Central
[/TD]
[TD]Graphic Design
[/TD]
[TD]GRDN-111
[/TD]
[/TR]
[TR]
[TD]10013
[/TD]
[TD]1001
[/TD]
[TD]2010-2011
[/TD]
[TD]2011-Q1
[/TD]
[TD]Central
[/TD]
[TD]Graphic Design
[/TD]
[TD]GRDN-115
[/TD]
[/TR]
[TR]
[TD]10014
[/TD]
[TD]1001
[/TD]
[TD]2010-2011
[/TD]
[TD]2011-Q1
[/TD]
[TD]Central
[/TD]
[TD]Graphic Design
[/TD]
[TD]GRDN-130
[/TD]
[/TR]
[TR]
[TD]10015
[/TD]
[TD]1001
[/TD]
[TD]2011-2012
[/TD]
[TD]2011-Q4
[/TD]
[TD]East
[/TD]
[TD]Fine Arts
[/TD]
[TD]FINA-100
[/TD]
[/TR]
[TR]
[TD]10016
[/TD]
[TD]1001
[/TD]
[TD]2011-2012
[/TD]
[TD]2011-Q4
[/TD]
[TD]East
[/TD]
[TD]Fine Arts
[/TD]
[TD]FINA-101
[/TD]
[/TR]
[TR]
[TD]10017
[/TD]
[TD]1001
[/TD]
[TD]2011-2012
[/TD]
[TD]2011-Q4
[/TD]
[TD]Central
[/TD]
[TD]Graphic Design
[/TD]
[TD]GRDN-120
[/TD]
[/TR]
[TR]
[TD]10018
[/TD]
[TD]1001
[/TD]
[TD]2011-2012
[/TD]
[TD]2011-Q4
[/TD]
[TD]Central
[/TD]
[TD]Graphic Design
[/TD]
[TD]GRDN-201
[/TD]
[/TR]
[TR]
[TD]10019
[/TD]
[TD]1001
[/TD]
[TD]2011-2012
[/TD]
[TD]2012-Q1
[/TD]
[TD]North
[/TD]
[TD]Graphic Design
[/TD]
[TD]GRDN-202
[/TD]
[/TR]
[TR]
[TD]10020
[/TD]
[TD]1001
[/TD]
[TD]2011-2012
[/TD]
[TD]2012-Q1
[/TD]
[TD]North
[/TD]
[TD]Graphic Design
[/TD]
[TD]GRDN-240
[/TD]
[/TR]
[TR]
[TD]10021
[/TD]
[TD]1001
[/TD]
[TD]2011-2012
[/TD]
[TD]2012-Q1
[/TD]
[TD]North
[/TD]
[TD]Graphic Design
[/TD]
[TD]GRDN-260
[/TD]
[/TR]
[TR]
[TD]10022
[/TD]
[TD]1002
[/TD]
[TD]2009-2010
[/TD]
[TD]2010-Q1
[/TD]
[TD]Central
[/TD]
[TD]Graphic Design
[/TD]
[TD]GRDN-100
[/TD]
[/TR]
[TR]
[TD]10023
[/TD]
[TD]1002
[/TD]
[TD]2009-2010
[/TD]
[TD]2010-Q1
[/TD]
[TD]Central
[/TD]
[TD]Graphic Design
[/TD]
[TD]GRDN-105
[/TD]
[/TR]
[TR]
[TD]10030
[/TD]
[TD]1002
[/TD]
[TD]2011-2012
[/TD]
[TD]2011-Q4
[/TD]
[TD]Central
[/TD]
[TD]Graphic Design
[/TD]
[TD]GRDN-120
[/TD]
[/TR]
[TR]
[TD]10031
[/TD]
[TD]1002
[/TD]
[TD]2011-2012
[/TD]
[TD]2011-Q4
[/TD]
[TD]Central
[/TD]
[TD]Graphic Design
[/TD]
[TD]GRDN-201
[/TD]
[/TR]
[TR]
[TD]10032
[/TD]
[TD]1002
[/TD]
[TD]2011-2012
[/TD]
[TD]2012-Q1
[/TD]
[TD]North
[/TD]
[TD]Graphic Design
[/TD]
[TD]GRDN-202
[/TD]
[/TR]
[TR]
[TD]10033
[/TD]
[TD]1002
[/TD]
[TD]2011-2012
[/TD]
[TD]2012-Q1
[/TD]
[TD]North
[/TD]
[TD]Graphic Design
[/TD]
[TD]GRDN-240
[/TD]
[/TR]
[TR]
[TD]10034
[/TD]
[TD]1002
[/TD]
[TD]2011-2012
[/TD]
[TD]2012-Q1
[/TD]
[TD]North
[/TD]
[TD]Graphic Design
[/TD]
[TD]GRDN-260
[/TD]
[/TR]
[TR]
[TD]10035
[/TD]
[TD]1003
[/TD]
[TD]2010-2011
[/TD]
[TD]2010-Q4
[/TD]
[TD]East
[/TD]
[TD]Fine Arts
[/TD]
[TD]FINA-100
[/TD]
[/TR]
[TR]
[TD]10036
[/TD]
[TD]1003
[/TD]
[TD]2010-2011
[/TD]
[TD]2010-Q4
[/TD]
[TD]East
[/TD]
[TD]Fine Arts
[/TD]
[TD]FINA-101
[/TD]
[/TR]
</tbody>[/TABLE]

...and with the following pivot fields:

[TABLE="class: grid"]
<tbody>[TR]
[TD]Column Labels
[/TD]
[TD]Fiscal Year
[/TD]
[/TR]
[TR]
[TD]Row Labels
[/TD]
[TD]Campus, Program
[/TD]
[/TR]
[TR]
[TD]Values
[/TD]
[TD]Student Count
[/TD]
[/TR]
[TR]
[TD]Measures
[/TD]
[TD]Student Count := DISTINCTCOUNT([Student No])
[/TD]
[/TR]
</tbody>[/TABLE]

...I get the following pivot:

[TABLE="class: grid"]
<tbody>[TR]
[TD]Location / Program
[/TD]
[TD]2011-2012
[/TD]
[TD]2010-2011
[/TD]
[TD]2009-2010
[/TD]
[/TR]
[TR]
[TD]Central
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]

Graphic Design
​

[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]East
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]

Fine Arts
​

[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]North
[/TD]
[TD]2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]

Graphic Design
​

[/TD]
[TD]2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Grand Total
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]

...but seeing as I'm looking to filter out all students not meeting the minimum registration count, and only count them once for their primary program at their primary location, I believe the following pivot is what I'm trying to create:


[TABLE="class: grid"]
<tbody>[TR]
[TD]Location / Program
[/TD]
[TD]2011-2012
[/TD]
[TD]2010-2011
[/TD]
[/TR]
[TR]
[TD]Central
[/TD]
[TD]
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]

Graphic Design
​

[/TD]
[TD]
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]North
[/TD]
[TD]2
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]

Graphic Design
​

[/TD]
[TD]2
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Grand Total
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

I successfully created a detached slicer to choose the minimum number of registrations to use in the fiscal year, but I don't know if that's the right way to start this. I imagine the same pattern will be used for both of the "Primary Metrics", and I attempted to sum registration counts in calculated columns, but that only resulted in errors and random garbage. I'd be most appreciative of any advice you can give!

 

[powerpivotpro]

Been messing with this for an hour and I gotta say, I hate this problem

Ideally this would be done using calculated columns that are generated in a database. But I haven't given up yet! I just need to sleep on it and I am positive an answer will be clear in the morning. Either that, or it won't

 

[miguel.escobar]

Idea why don't you create a calc column in your table?

=CALCULATE( COUNTA([Program]), FILTER(Table1, [Fiscal Year] = EARLIER([Fiscal Year])), FILTER(Table1, [Student No] = EARLIER([Student No])) )

https://dl.dropbox.com/u/54063091/Book1.xlsx that will give you the amount of courses that the student had for the specific FY. You can adjust the formula to your needs but I don't think I'm clear on what you're aiming to get, could you rephrase on what you're trying to achieve? do you need a count of all the students that have >=3 courses during the FY? do you need a rank of the courses to see which one had the most students enrolled? do you need a rank on the campus that had the most students? HOPE THIS HELPS!

 

[spudsy]

I'm humbled that you'd spend the time Rob, I hope it wasn't too frustrating! You, sir, are bringing BI to the masses

 

[spudsy]

Thanks for your example Miguel! I'll give it a shot at work in the morning, but in the meantime, what I'm trying to do is have one student only be counted once per Fiscal Year (if they have 3+ registrations). Student may be enrolled in multiple Programs at multiple Campuses within the year, but I'd like (or have been requested) to have them only be counted once in their "best" or "primary" Program and Campus, maybe like a next step beyond a distinct count... I'll try to explain this better in the morning!

Thanks again!

 

[spudsy]

Maybe the following screen cap can help illustrate what I'm looking for:

(Sorry, Reg No. 10011 should be highlighted as well)

- Double underlines denote a break between Student Numbers
- Single underlines denote a break between Fiscal Years
- The highlighted green records are the only enrolments that should be counted towards the final headcounts

Regarding the "Primary Campus", let's take Student No 1001 as an example:
- In the 2011-2012 Fiscal Year, there are 2 records for the East Campus, 2 records for Central, and the 3 highlighted records for North
- North has the highest registration count, so this is what I would then consider the "Primary Campus"
- In the pivot I would like Student No. 1001 to be counted once in the 2011-2012 FY column, at the North Campus only, and not include them in the headcount for the East or Central Campus line-items

Does that clear things up in the slightest? Sorry if I'm using ambiguous terminology, I'm not sure of the best way to explain this.

I may be going off on a complete tangent, but I was playing around with adding a calculated column to flag a registration as matching the "Primary Campus", but can't quite understand how to wrap the TOPN/SUMMARIZE function below to do anything further:

=TOPN(
  1, SUMMARIZE(
    FILTER(Table1
      , [Student No] = EARLIER([Student No])
      && [Fiscal Year] = EARLIER([Fiscal Year])
    )
    , [Campus]
    , "Reg Count", COUNTA([Reg No])
  )
  , [Reg Count]
  , 0
)

I think I want to return a boolean from a comparison against with the Campus column, but I don't know how to as TOPN and SUMMARIZE return a table.

I know I'm missing something (or two ), maybe I should be focusing on getting the flags populated before I import into PowerPivot as Rob suggested... but PowerPivot has to be able to do this, doesn't it?

Thanks again for any help you can provide!

 

[MD610]

I may have a solution that works. It's probably not the most elegant but it seems to work well on the sample data and I even added a few additional rows to see if it would trip up.

It requires 4 Calculated Columns and 3 Measures. I'm sure some could be combined but this was easier to troubleshoot:

1. First add 2 simple measures to count the Registrations and the unique number of students:

Cnt Reg No:=COUNTA(Table1[Reg No])

Student Cnt:=DISTINCTCOUNT(Table1[Student No])

2. Add 2 Calculated Columns to identify the Primary Campus and Primary Program:


Campus Cnt =CALCULATE([Cnt Reg No], FILTER(Table1, Table1[Student No] = EARLIER(Table1[Student No]) && Table1[Fiscal Year]= EARLIER(Table1[Fiscal Year]) && Table1[Campus] = EARLIER(Table1[Campus])))


Program Cnt =CALCULATE([Cnt Reg No], FILTER(Table1, Table1[Student No] = EARLIER(Table1[Student No]) && Table1[Fiscal Year]= EARLIER(Table1[Fiscal Year]) && Table1[Program] = EARLIER(Table1[Program])))

3. Now add a 3rd Calc Column for ranking the Campuses in tiebreaker situations:


Campus Rank =IF(Table1[Campus] = "Central", 1, IF([Campus]="North", 2, 3))

4. Now add a 4th Calc Column that returns the lowest Campus Rank for each student in each fiscal year for each program in each quarter:


Primary Campus =CALCULATE(MIN([Campus Rank]), FILTER(Table1, Table1[Student No]=EARLIER(Table1[Student No]) && Table1[Fiscal Year]=EARLIER(Table1[Fiscal Year]) && Table1[Program] = EARLIER(Table1[Program]) && Table1[Term] =EARLIER(Table1[Term])))

5. Finally you can add the measure that will be used in your pivot and refereces all new Calc Columns:

Final Measure:=CALCULATE([Student Cnt], FILTER( Table1,Table1[Campus Cnt] >=3 && Table1[Program Cnt] >= 3 && Table1[Campus Rank]=Table1[Primary Campus]))

Like I said, not the most elegant, but I believe it works and returns the result you need. You could replace the hard coded 3's in last measure with your disconnected slicer measure, but that is probably something to do after you have the main logic figured out.

If this doesn't work, I think it's probably close and hopefully it can inspire some ideas to reach the final goal you do need.

 

[miguel.escobar]

I wonder if the primary campus is a field that could be set by the student. I know that some students get to choose what's their main course, campus and such....is your query or table missing some fields that could hopefully help you solve this problem? If not, then there's no simple way to do it since we need to define so many tie breakers and other parameters. BTW, MD610's answer was the one that I was going to write in this posts but he's a faster at typing than I am

 

[MD610]

I agree with Miguel that to solve this completely, additional info may be necessary. That is why I used the Term in one of my measures. I believe I needed that extra field to make a unique grouping in the calc column.

My final pivot is correctly including the highlighted rows, but it's hard to tell if it is just working on the limited sample data set.
[TABLE="width: 232"]
<TBODY>[TR]
[TD="width: 127"]Final Measure
[/TD]
[TD="width: 114"]Column Labels
[/TD]
[TD="width: 68"][/TD]
[/TR]
[TR]
[TD]Row Labels
[/TD]
[TD]2011-2012
[/TD]
[TD]2010-2011
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Central
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Graphic Design
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]10011
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]10012
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]10013
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]10014
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]North
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Graphic Design
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]10019
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]10020
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]10021
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]10032
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]10033
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]10034
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65"]Grand Total
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[spudsy]

Ah, this looks really promising! I'll have to spend some time on it tomorrow to hopefully understand the complexity of the Primary Campus. There's sadly nothing I can do about pre-determining the Campus beyond processing it beforehand in Access (or I could potentially load it up in SQL Server), as my sample is based off the final data snapshot available for this report. There are a number of other columns in the real dataset, however the count of Registrations per Campus is all we have to go on for this, as with the Registration count for the Primary Program.

You guys are awesome, thanks for all your time (so far )!