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

    Need Help in developing formula

    Muhammad Khan

      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

            

            

      ClassNumber of students Course 1 Passed byCourse 1 Flailed byCourse 2 Passed byCourse 2 Fail by
      Class 14422222123
      Class 210100100
      Class 322220182
      Class 433330330
      Class 515105114
      Class 61010101010

       

      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

       

       

      Muhammad

       

      Pooja GandhiMahfooj Khan

        • 1. Re: Need Help in developing formula
          Tharashasank Davuluru

          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
            Muhammad Khan

            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

             

            Muhammad Khan

            • 3. Re: Need Help in developing formula
              Luciano Vasconcelos

              See this one.

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

                Hello Luciano

                 

                Thank you so much, It really worked.

                 

                Regards

                 

                Muhammad

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

                  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
                    Muhammad Khan

                    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

                     

                    Muhammad