6 Replies Latest reply on Aug 22, 2016 11:11 AM by Muhammad Khan

# Need Help in developing formula

Hello Everyone

A quick question, I have 4 measures  with range of 1 to 100 in which 60 is passing marks. which formula should I use to show pass and fail marks separately for every individual student ? When I am filtering it with range values then it applies to entire measures.

Measures

Course 01  Values 1 , 2, 3 ......100

Course 02 Values 1 , 2, 3 ......100

Course 03 Values 1 , 2, 3 ......100

Course 04 Values 1 , 2, 3 ......100

I am trying to do it in following way

 Class Number of students Course 1 Passed by Course 1 Flailed by Course 2 Passed by Course 2 Fail by Class 1 44 22 22 21 23 Class 2 10 10 0 10 0 Class 3 22 2 20 18 2 Class 4 33 33 0 33 0 Class 5 15 10 5 11 4 Class 6 10 10 10 10 10

I am trying to create formula like this

Calculated filed name (Course 1 Passed by)

COUNT(IIF ([Course 01]) <= 59  THEN [Number of Students] END)

your help in this regard will be highly appreciated.

Thanks

• ###### 1. Re: Need Help in developing formula

Hi Khan,

In the data what you have provided it is showing the no.of students who passed in course 1 , course2, course 3,. How your formula will work there is no column for data or marks. please explain it more.

provide a sample work book.

• ###### 2. Re: Need Help in developing formula

Hello Tharashasank

Thank you so much for your reply, Course 1 is actually  marks (measure). And "course 1 passed by" is my proposed calculated filed.

COUNT(IIF ([Course 01]) <= 59  THEN [Number of Students] END)

In the given formula Course 01 is marks. If you need further clarification kindly let me know.

Thanks

• ###### 3. Re: Need Help in developing formula

See this one.

2 of 2 people found this helpful
• ###### 4. Re: Need Help in developing formula

Hello Luciano

Thank you so much, It really worked.

Regards

1 of 1 people found this helpful
• ###### 5. Re: Need Help in developing formula

1. Dropping the field "[Class]" in Rows Bar & "[Number of students]" in Columns Bar will give you the first 2 columns using the text graph in Show Me menu.

Secondly, you want to create a field which calculates the number of studens who passed various courses. Hence, you need to make a calculated field [Course 1 passed by] as

COUNT(IIF ([Course 01]) >= 60  THEN TRUE END)

It will count the number of "TRUE" and when dragged in the Columns bar, it will show the corresponding number of students who passed the course in a given class.

[Course 1 failed by] as

COUNT(IIF ([Course 01]) <= 59  THEN TRUE END)

It will count the number of "TRUE" and when dragged in the Columns bar, it will show the corresponding number of students who failed the course in a given class.

In no case should the number of passed & failed students exceed the total number of students in a class.

Similarly, make other calculated fields like

[Course 2 passed by] as

COUNT(IIF ([Course 02]) >= 60  THEN TRUE END)

[Course 2 failed by] as

COUNT(IIF ([Course 02]) <= 59  THEN TRUE END)

2. Another simple but lenghty approach is

calculated field [Course 1 failed by] as

(IIF ([Course 01]) <= 59  THEN TRUE END)

While dropping the field onto the Columns bar, change the Measure to "Count" by right clicking on it.

This way has an advantage as you can use it as a filter or to color code the students who failed or passed a particluar subject in a specific class(es).

2 of 2 people found this helpful
• ###### 6. Re: Need Help in developing formula

Hello Pulkit

Thank you so much for your reply, I like your approach. the issue is solved but I will consider your approach in other steps of my report.

Rergards