Percentage in drill down
I am getting zero value for percentage, when i am doing drill down.
I have three tables, Two dimension and one fact table.That is Region dimension and vehicle dimension. I am taking the vehicle information from vehilce dimension, region from region dimension and new and renewal measure from fact table. I need to find in region how many new and renewal vehicles are registerd based on the type of vehicle. say for example in the region1 how new car howmany renewal car. and i need to find total new and total renewal for each region and gtotal(new plus renewal) and percentage.
So i am using crosstab to display the information as per requirement. I am getting all correct result. for total new total renewal, grand total and percentage values. But when i am doing the drill down i am getting some strange result. That is if new contain zero value means the percentage also showing the zero value, but in the same case i am containing the value for renewal so the grand total diaplying the renewal value correctly. but percentage showing as the zero. if both new and renewal containg the value means it is givinf the correct result.
Plz help me to fixz the problem, and which formuls i need to use to find the percentage. Right now i am using the Percentage(gtotal);Col)*100