Calculating Student Tuition Balance

[CFink15]

Hello,

I am working on a dashboard linked to our student billing system that administrators can use to track outstanding student tuition balances. The dashboard updates with live information from the billing system. What I'd like to be able to do is have several pages based on different criteria (international students, dorm residents, and others), and only show students who have outstanding balances (i.e. balances greater than $0).

I've tried the following:

1) Merge billing data query with itself to aggregate Billing Amount by Student ID

• This is not feasible because once the queries are merged, the new column is static and does not update when new billings are entered into the system

2) "Totals" quick measures - Billing Amount as Base value, Student ID as Category/Field

• Accumulates total of entire student population rather than accumulating by individual student

3) Added a Matrix visualization with Student ID as rows and Billing Amount as values

• When I apply a filter Billing Amount > 0, it shows me all individual Billing Code lines that are greater than 0, rather than showing the grouped student balances that are greater than 0.

Here's an example of the student billing format. The Target - Student Balance is the column/measure I want to create that will allow me to filter by student balances that are greater than $0.

Student ID	Billing Code	Billing Amount	Target - Student Balance
P000040342	COCONTFEE	850	638
P000040342	CR NELNET	-212	638
P000061610	COCONTNON	250	250
P000079211	COCONTFEE	850	987
P000079211	COHLINSGR	2,787	987
P000079211	COHLCTR	650	987
P000079211	CRNLNTACH	-3,300	987

Any suggestions on how to add a Measure that shows balances grouped by Student ID? Thanks in advance.

[DataInsights · Accepted solution]

@CFink15,

Try this measure:

Target - Student Balance = 
CALCULATE ( SUM ( Table1[Billing Amount] ), ALLEXCEPT ( Table1, Table1[Student ID] ) )

[CFink15]

Thanks a ton, @DataInsights! Such a simple solution. I appreciate it.