cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tnicholson
Frequent Visitor

Calculation derived from measure

I am new to PowerBI, and so far, I'm not a big fan. I am struggling with a particular project I'm working on for a client. The client is a university that wants to aggregate data by the enrollment status of students (i.e. full-time or part-time). The caveat is that this needs to be done on a running total of credit hours for each week of registration. I believe I have the running total calculating, but I need to be able to set the enrollment status for that week based on the running total. Additionally, the number of credit hours used to define the status varies based on whether the student is an undergraduate or graduate. Full-time for undergraduates would be 12 or more credit hours and 6 or more credit hours for graduates. I want a table similar to this:

 

Student IDLevelWeekRunning Credit HoursEnrollment Status
123UG19PT
123UG212FT
123UG36PT
456GR16FT
456GR26FT
456GR36FT

 

I find it extremely difficult to get the running credit hours measure to display in a column, and also to simply refer to the running total for that row to see what the enrollment status should be. The condition would be

IF([Level]="UG",IF([Running Credit Hours]>=12,"FT","PT"),IF([Running Credit Hours]>=6,"FT","PT"))

I tried using this in a new column, but it did not work. How would I go about building this?

1 ACCEPTED SOLUTION

here is another tutorial specifically for running totals calculation

 

https://youtu.be/3-I46u5saOA

View solution in original post

8 REPLIES 8
andre
Memorable Member
Memorable Member

when you add a new column in power bi, your formula only sees the current row by default, so you need to use a CALCULATE functin and a filter claus to open up your table so as you are looking at the current row, you can also see all the rows before.  

 

unrortunately, this is not easy for a beginner to do, maybe this link can help you with understanding of how to see more than one row in the table as you add a new column:

https://businessintelligist.com/2014/08/19/adding-an-index-column-to-a-power-pivot-model-using-earli...

 

Thank you @andre, that gets me closer to what I need. I am now able to see the correct enrollment status per week. Now my next conundrum is how to then pivot on that FT/PT value. The client wants to see the number of students and the credit hours broken out by FT and PT.

tnicholson_0-1595443789732.png

This pivot should have 4 columns with count and total credits for FT and the same for PT.

If I try to add the FT/PT calculation to the pivot table columns, it won't let me. I can only add it as a Value.

Anonymous
Not applicable

You need to use this matrix 

 

2EEDA4C6-6E27-4325-9A23-AB285D0F40FE.jpeg

then place school in rows 

ft/pt istatus in columns

and then your measures in values. 

this should give you the 4 columns your looking for 

 

Yes, but unfortunately, FT/PT is a calculation derived from the running total, and as such, cannot be used in columns. That is my conundrum. I need to be able to break out the headcount and credit hours into separate columns for FT and PT.

Anonymous
Not applicable

if your its in a calculated column it will work 

 

if you send me a sample i can whip it together

here is another tutorial specifically for running totals calculation

 

https://youtu.be/3-I46u5saOA

Thank you @andre! That did it! 

no worries, glad it helped

Helpful resources

Announcements
collabdays lisbon

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.