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.
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?
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.
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
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.
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.
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?
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
if i add
i get an error saying you cannot mix aggregate and non aggregate functions in an if statement.
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.