Counting YAGO data by nationality

[dgroyal]

I'm trying to create a table that shows counts of student enrollment for a given term (aka semester), by nationality, alongside the count of the same nationality for the YAGO term.  Ultimately, I'd like to show variance (ie 6 if up 6, -5 if down 5) and % variance.

I've tried several different approaches (including multiple table occurences, joined on a term -> term yago) but the problem is that nationality breakdown is lost on the YAGO data.  In other words, for every row of data, it shows the TOTAL YAGO count, rather than the count just for that nationality:

My current approach is doing this: 

CountYAGO = CALCULATE ( SUM ('FM SIS'[z_CountOne]), ( FILTER (ALL ('FM SIS'), 'FM SIS'[Term] = ( MAX('FM SIS'[Term]) - 100 ))))

z_CountOne is just a field with the value of 1, and our term structure is 201801 (for Spring 2018) so subtracting 100 gets me YAGO.

My table currently shows:

NATIONALITY | COUNT | COUNT YAGO

China               | 183      | 2248

Saudi Arabia    |  82       | 2248

2248 is our TOTAL number of students YAGO, but I want it shown by nationality.

Thanks!

[parry2k]

@dgroyal please remove ALL( 'FM SIS') from your formula and change it to 'FM SIS'

[dgroyal]

Thanks @parry2k .  I did that, but now I get no value in my CountYAGO column.

[parry2k]

@dgroyal wihtout knowing how your data is structured it is bit tought, Can you share sample data? What is the purpose deducting 100 ?

[dgroyal]

Sure, @parry2k .  Deducting 100 from TERM basically does the same things as deducting 1 from YEAR would.

Here's some sample data:

https://www.dropbox.com/s/0zwf8v9g1k4sh99/BI_SIS_sample.xlsx?dl=0

https://www.dropbox.com/s/i7wvytr2t8oemg1/BI_Student_sample.xlsx?dl=0

I'm trying to show current term and YAGO (aka Term - 100) counts for the SIS file (aka Student in Semester) by nationality, which is in the Student file.  Thanks!

---

[parry2k · Accepted solution]

@dgroyal i guess attached will help

[dgroyal]

Thanks so much!  I used that calculation in my solution and it works perfectly.  I really appreciate your help!