Get percentage of most upper part in funnel
I'm using Sql Server 2008 Standard R2
I created a funnel chart which contains the number of people in the first activity of a project, then the number of people in the second activity of a project etc etc.
Everybody starts with the first activity, but after that some people quit, so less people start with the second activity. And after that some people quit, so even less people start with activity 3.
Now I want the first activity (the most upper part of the funnel) to be 100%, and the second activity is the number of people started with activity 2 compared to the number of people originally started with activity 1.
e.g
activity number of people percentage
act 1 783
100%
act 2 606 77.39%
(606/783 * 100)
So, the category group in the graph is activity.
How to make the exrpession for the data label in the funnel graph, to get the correct percentages?
Regards, Hennie
June 27th, 2012 4:04am
Hi Hennie,
Based on your scenario, we can achieve the requirement by customizing the label expression. If the number of people value for each activity is an aggregate value, you can set the label expression like below:
=IIF(Previous(Sum(Fields!number_of_people.Value)) Is Nothing,"100%",FormatPercent(Sum(Fields!number_of_people.Value)/IIF(Previous(Sum(Fields!number_of_people.Value)) Is Nothing,1,Previous(Sum(Fields!number_of_people.Value))),2))
If the number of people value for each activity is not an aggregate value, then, you can set the label by using the following expression:
=IIF(Previous(Fields!number_of_people.Value) Is Nothing,"100%",FormatPercent(Fields!number_of_people.Value/IIF(Previous(Fields!number_of_people.Value) Is Nothing,1,Previous(Fields!number_of_people.Value)),2))
If you have any questions, please feel free to let me know.
Regards,
Mike Yin
TechNet Subscriber Support
If you are
TechNet Subscription user and have any feedback on our support quality, please send your feedback
here.
Free Windows Admin Tool Kit Click here and download it now
July 1st, 2012 8:12pm