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!

 

[Laurent C]

The TOPN approach failed because the TOP N is calculated for all student, and fiscal years, in the current selection. It will (most of the time) only return one row.

However, this is my preferred approach.

You could use the GENERATE function to do a TOPN for each relevant combination.

GENERATE(  
  SUMMARIZE( 'Table1'
   ,  'Table1'[Student No], 'Table1'[Fiscal Year]
  )
  , TOPN(1,
   SUMMARIZE( VALUES ('Table1')
     , 'Table1'[Campus]
     , "Reg Count" ,  CALCULATE(COUNTROWS('Table1') )
   )
   , [Reg Count]
    )
    
 )

The first summarize will find the relevant combinations of [Student No], [Fiscal Year]. Then for each combination, do a TOPN. This requires to a CALCULATETABLE expression because of the new row context.

To calculate the number of courses taken for the preferred campus, then plug this table expression in a CALCULATE expression.

[Number of course on primay campus]
=CALCULATE( COUNTROWS(VALUES('Table1')),
      GENERATE(  
  SUMMARIZE( 'Table1'
   ,  'Table1'[Student No], 'Table1'[Fiscal Year]
  )
  , 
   TOPN(1,
    SUMMARIZE( VALUES ('Table1')
     , 'Table1'[Campus]
     , "Reg Count" ,  CALCULATE( COUNTROWS('Table1') )
      ), [Reg Count]
   )
    
 )
)

This is a quick approach to the problem. This is not fully tested and I am not sure this fits your requirements.
Note the formula will do a TOPN amongst all values of Campus that are in the current selection. Also, TOPN may return several rows in case of a tie.

 

[spudsy]

Wow Laurent, that's really helping me a lot with how GENERATE and SUMMARIZE work!

However, could you help me understand what you mean by:

This requires to a CALCULATETABLE expression because of the new row context.

...especially since I don't see a CALCULATETABLE in the expression Forgive me if I'm just missing something.

And as it turns out, I do get many duplicate matches using TOPN, could I pass the process through FIRSTNONBLANK at some step to remove the duplicates?

Very cool, and much appreciated!

 

[miguel.escobar]

basically it's just evaluating (row context) a table prior to any other "nodes" or aggregation taking place...It's like you're creating a really cool table to help your measure work like you want it to
that's in simple terms...for more info hereCALCULATETABLE Function

 

[Laurent C]

You did not miss anything ... sorry for the confusion. If I remember correctly, the first version of the measure had no CALCULATE in the "Reg Count" expression but CALCULATETABLE around the TOPN expression.