9 Replies Latest reply on Oct 12, 2016 12:43 AM by Ben Whitaker

    Counting dimensions

    Garima Vohra



      I have data with all the fields as dimensions. The first Column is ID which is a combination of Letters and Numbers. Just like excel shows us the total count of rows, I want to incorporate something- a variable or a calculated field to be able to count how many IDs/rows are shown in my text table. How can this be achieved, please share.


      Thanks in advance


        • 1. Re: Counting dimensions
          Peter Gabe



          You can make a calculated field called index(), convert to discrete and drag that into your rows so it's the first one.

          • 2. Re: Counting dimensions
            Matt Hong

            There is a default calculation called 'Number of Records' under measures, which will show you the # of rows when double-clicked.

            • 3. Re: Counting dimensions
              Garima Vohra

              Hello Matt,


              Thanks for the response. The number of records thing shows count of each row that is it places 1 at the end, I want to have the total which it's not showing.


              Thank you

              • 4. Re: Counting dimensions
                Garima Vohra

                Hi Peter,


                Thanks for the reply. I tried the index(), but as I said my fields are dimensions and that's why it is giving error when I am using a string field within index().



                • 5. Re: Counting dimensions
                  Matt Hong

                  It shows the total if you don't have anything on the columns or rows. Create a new sheet and just double-click on Number of Records

                  • 6. Re: Counting dimensions
                    Garima Vohra

                    Hi Matt,

                    I tried what you suggested above, but when I put the ID, Gender like fields along with Number of records it shows numbers per row like 1,2,1 and the like, not the total. Total count of records is only visible if there is no other field on the shelf. I appreciate your help response a lot. Do you have any suggestion on how to attain this?.


                    Thank you so much


                    • 7. Re: Counting dimensions
                      Ben Whitaker

                      If I am understanding this, I think you just need an LOD...


                      { FIXED :SUM([Number of Records])}


                      Or list all of the dimensions in your view using an exclude:


                      { EXCLUDE [Gender],[any other dims in view] :SUM([Number of Records])}


                      Or use a table calc:


                      TOTAL(SUM([Number of Records]))


                      All give you a total count ignoring what you have in your view. Hope this helps.




                      1 of 1 people found this helpful
                      • 8. Re: Counting dimensions
                        Garima Vohra

                        Hi Ben,


                        Works perfect. Really appreciate it, just one question it looks like (example) this


                        User IDNumber of Records



                        Is there any way I can just have one row in the end, showing the total just once?


                        Thanks a lot

                        • 9. Re: Counting dimensions
                          Ben Whitaker

                          HI Garima,


                          Do you want to just have a table of values with a total at the end? For example:


                          User ID      Number of Records

                          ES47856          10

                          ES48965          15

                          Total                25


                          If so, you don't need an LOD, you can just use the number of records field and then go to Analysis  > Totals > Show Column Grand Totals. If you want to show 25 on every record, then you need an LOD or a table calc like I describe above.


                          If not, could you upload a packaged workbook and I will take a look at what you want?