8 Replies Latest reply on Feb 6, 2017 9:36 AM by Jim Dehner

    Calculated Field

    Susan Kurian



      I have attached a spread sheet which contains test data. What I would like to do is create a filter with "School1" and "School 2" as a filter. If School 1 is selected then I need the Individual rows displayed for "Ed Cur" field, "Ed Ped" field and "Ed scd" with sum of values.


      And if School 2 is selected, I need individual rows displayed for  "Iaps Gen", "Iaps MHC" and "Iaps Phd" with the sum of values


      How would I accomplish that?


      Please Advise.




        • 1. Re: Calculated Field
          Jim Dehner



          Could not open your excel file - says it is corrupt could you resend


          • 2. Re: Calculated Field
            Susan Kurian

            Hi Jim,


            I have attached the file again.




            • 3. Re: Calculated Field
              Khang Pho

              Hi Susan,


              One method would be to pivot the columns so you can have the values in the rows.  From there you can create a calculated field like


              IF [School Desc] = 'School 1' AND 
              ([Pivot Field Names] = 'Ed Cur' or [Pivot Field Names] = 'Ed Ped' or [Pivot Field Names] = 'Ed Scd')


              [School Desc] = 'School 2' AND
              ([Pivot Field Names] = 'Iaps Gen' or [Pivot Field Names] = 'Iaps Mhc' or [Pivot Field Names] = 'Iaps Phd')


              [Pivot Field Values]



              If you then filter out the nulls I think it should give you the desired effect.

              • 4. Re: Calculated Field
                Jim Dehner

                Hi Susan


                Thanks for re-sending the data - I agree with with Kang that pivoting the data is the way to go

                I did it at the data source level leaving a set of values and field names

                Then I did set up a filter Show Data >> If Value >0 then 1 End

                Drag the filter to the filter shelf and set the value to 1

                drag fields to the columns and schools to the row

                Drag a schools tot the  filter shelf and show the filter - when you select schools the null field disappear


                Here is a T10.1 copy


                it should look like this




                Let me know if that helps



                1 of 1 people found this helpful
                • 5. Re: Calculated Field
                  Susan Kurian

                  Hi Jim,

                  Would you please show me, how did you pivot the data? Your Screenshot; looks exactly how I wanted to display it.



                  • 6. Re: Calculated Field
                    Jim Dehner

                    Good morning


                    see the link below to the on-line hel


                    Pivot Data from Columns to Rows


                    I will walk you through it here


                    Go to the data source tab


                    Identify the columns that need to be pivoted (it you data)

                    Click on the header for the first column (Total Number)

                    Shift click the header for the column on the right most of your data (Ed scd)

                    While all the selected columns are highlighted Right click the last column and click on Pivot Data on the box that opens

                    the several columns that were highlighted will now become 2 columns that you can rename - I just chose Fields and Values


                    That's all there is to it


                    In your viz - Fields becomes a dimension that you add to viz and the Values are the Measures


                    Let me know how it goes



                    • 7. Re: Calculated Field
                      Susan Kurian

                      Hello Jim,


                      It works like a charm.. Thank You so much.



                      • 8. Re: Calculated Field
                        Jim Dehner

                        Glad to help you out


                        If you need anything else let me know

                        Thanks for the badge  - always appreciated