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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics