This is what I did, I changed the default to Sum Then added total to the rows but removed the total from the columns...so it seems you are right, I think my boss would like more to see the total by columns , we will see. thanks

@Christian, did you mean to say that I can have only one of the two? and not both? by rows and columns? yes I have the RSUM aggregated formula by region in there to show week over week.

Thanks again but I want to be able to show the number aggregated week after week for each region, that is why I need it to be RSUM and at the same time I want to show the grant total for each week- so that is not possible?

My RSUM is to aggregate the revenue week over week by region as you see in my image for the entire quarter but then I need to show the total for all regions for each week.

I am not summing again in pivot, do you suggest I dont edit the formula and sum it in pivot? how should I do that?

I redid it from the beginning and it worked, thank you @Gianni Ceresa

Thanks @Gianni Ceresa I tried that but it did not work...do I have it wrong? here is the image of my formula But then I ended up with this

Thank you Christian

Thank you so much @Gianni Ceresa

@Joel Acha How do you suggest the prompt should be created- because are few ways of doing right?

I see what you are saying...here is my case statement CASE WHEN "Bill Customers"."Bill Customer Party Number" = "Ship Customers"."Ship Customer Party Number" THEN 'Sell to' ELSE 'Sell thru' END

Thank you Christian for the helpful information, much appreciated. Joe

I even changed by criteria, I removed the order number just in case that is causing the issue. So now I only have 3 columns, Division or region, order channel, SUM(0) on one SA and on the other I have the Division, Null, Amount...so I am trying to union the order channel if this is the right term. I was hoping to see LAD…

Thank you for pointing it out. I dont manage the RPD, I only create reports in the user interface. But I told them after finding out that the order number in one of the SA is a text. When you go to the order number column prosperity then data format...it is text but until they change it I went and converted that number…

Thank you both- I had an idea that I needed to convert the string to number but was not sure how and now I guess I need to read more and more about the CAST function in OBIEE. Thanks again. Joe