I am doing a Dashboard where I have to show a pivot table which has the data about the Plan Cost and Actual cost.Actual cost is working fine in the Table.I have a problem with the set analysis expression.
I will give you a brief description of what I have and What I need that might help you in resolving the problem.
I have a fact table named Trip details in that I have a field called Travel date. So using this travel date I have created one more table called Calendar which is joined to the Trips Details(Fact table).
I have one more field in the Trip details table named Region .I was given a excel with the region wise targets which looks like below
So I have used the cross table to load this data. I have connected the above table to the Fact table using Region field.
So now my requirement Is I want to show a pivot table with actual and plan values – region wise.I have the quarter, month and region as the dimensions.
Yes I have created the month field in such a way that it gives me the year info also.
month(TRAVEL_DATE) &' ' & year(TRAVEL_DATE) as Month,
In this I am getting the actual cost fine.I have a problem with the Planned cost.
It should show me the values of the targets which are defined in the first attached table.So for this I have written the set analysis.Might be what I have written is wrong.Please correct me if it is wrong and let me know how to get the correct figures for the planned cost.