12 Replies Latest reply on Sep 7, 2016 11:25 AM by Stephen Rizzo

    Best way to build this

    Austen Robinson

      I was wondering if anyone can give ideas on how to go about building this:



      the data is going to be a lot of measures and calculated fields.  Is it best to try to do layout containers or is their a better way? 

        • 1. Re: Best way to build this
          David Li

          Hi Austen, this depends on how the data is laid out. For instance, is each column an independent measure (and not a key in a dimension)? You might be able to do this just in a single sheet if you give us more details.

          • 2. Re: Best way to build this
            Austen Robinson

            Hi David.  This is just a grand total of certain measures formatted in this way.  So essentially take Maine, New Hampshire and Vermont.  These totals come from measures that have dimensions with state abbreviations like ME, NH and VT. 


            So would I need a calc field to do this?  So something like


            if [state] == "NH" then sum([booking target]) end   etc.


            or is there an easier way?


            the main thing that is confusing is how to get all the totals and grand totals formatted in the way that the OP screen shot look like?

            • 3. Re: Best way to build this
              Austen Robinson

              I got this far. 




              how do you get the amounts all in the first column?

              • 4. Re: Best way to build this
                Ryan Shirley

                Your columns are just measures (either from your data, or new calculated fields that you made), but it looks like your rows are made up of 2 (or more) different dimensions. Based on your screenshot, your Target Bookings Grand Total of $295,900 doesn't equal all the items below it (I can't even tell what items that number is supposed to be from), so I assume that number is made up of some other group of data, or mix of data than the one being shown. Since it seems to be made up of multiple measures your best bet might be to create 4 sheets:

                1 for your grand total where you just put your measures on the sheet, and use the filter shelf to get the total you need

                1 for your "NNE, Gov-Ed, etc" data - just duplicate the grand total sheet, all the dimension to get the rows you need, and then hide the headers

                1 for your "Enterprise, Business, Etc" data - same as above

                1 for your state data - same as above


                then put each of those 4 sheets on a dashboard to create the mashup that you want and give you the outcome as shown in your screenshot.


                The bad thing is that this would be a pretty static report, only being based off this one set of data and how you wanted to see it...if you wanted to see something else you would have to change all the underlying sheets which would be time consuming.

                • 5. Re: Best way to build this
                  Ryan Shirley

                  Don't create a calculated field for the state specific sales (that is why each of your columns have 2 null values, because your field is for sales for only ONE state). Just put state on the row shelf, and then drop in your "Booking Target" field; that is your first column. Then drop in you "Bookings" field; that is column 2...keep dropping in fields until you have all the data in there that you need

                  • 6. Re: Best way to build this
                    Ryan Shirley

                    This is from the Superstore data, but it essentially works the same way:



                    Just drop 2 or more measures into the window and make sure the "Measure Names" is on the column shelf.

                    • 7. Re: Best way to build this
                      Austen Robinson

                      not that easy as the totals depend on what is in several other dims on that row.  example Sam, Don, Sue and Tom all have sales in Maine.  I only want to total Sam and Don not the entire measure.  So it is conditional.  Hence the calculated fields. 

                      • 8. Re: Best way to build this
                        Ryan Shirley

                        That was what I was trying to explain in my first reply. You are going to have to put certain filters on certain sheets to show you the information you need. If Sue and Tom aren't supposed to be in your Maine totals, then you need to either 1) filter them out, or 2) create a new Booking Target calculated field which excludes them based on the (I assume) sales person dimension (Something like this: if [sales person]="Sue" or [sales person]="Tom" then 0 else "Booking Total" end) - it would be easier to just drop sales person on the filter shelf and then exclude those 2.

                        • 9. Re: Best way to build this
                          Austen Robinson

                          whats wrong with this calc


                          if [Channel] == 'Business' and  [Title] == 'AVP' then [Booking Target] end


                          there is one record that meets this criteria.  the result is zero.  Order of operations?

                          • 10. Re: Best way to build this
                            Ryan Shirley

                            Booking Target is a measure, so you need to use a sum function to pick it up. Just replace with Sum([Booking Target]) and you should be fine

                            • 11. Re: Best way to build this
                              Austen Robinson

                              if i add


                              sum([booking target]}


                              i get an error saying you cannot mix aggregate and non aggregate functions in an if statement.

                              • 12. Re: Best way to build this
                                Stephen Rizzo

                                See my post in the other thread.


                                Do you have a sample workbook you could share? My guess is that you might be able to do this more simply.