1 2 Previous Next 17 Replies Latest reply on Sep 29, 2016 6:17 AM by rrakesh.babh

# Help me create a pie chart by combining selected slices

I am trying to create a pie chart with 'Department Names' dimension and 'Student Id' measure.

When the user selects single/multiple Departments from the 'Department' filter, the pie chart should display the percentage of the No. of students of the corresponding department(s).

I have created it to some extent where in all the selected department's students count is shown in the pie chart as different slices visually and mathematically. The pie chart is displaying each department with a different color/slice and its associated percentage count to total count of students of all the departments.

I need to club/combine all the selected departments into a single slice/color and a single sum of percentage. Also it should display the unselected students in a 'Remaining'/'Other' category with its associated color/slice and percentage count of students.

I hope I am clear. Please accept my appologies for not uploading any data due to its sensitivity.

Rr

• ###### 1. Re: Help me create a pie chart by combining selected slices

Hi Rrakesh,

Static way will be create a set for those departments, and bring the set to color, showing in&out. Therefore we can have the comparison between those two sets.

Dynamically way could be using table calculation filter, I am still working on it. A Jedi (Filter and Table Calc) Trick | Tableau Software

One question comes to my mind is for one student, he/she just come from one department or might come from multiple department? Cos this will effect the calculation regarding total count of students of all departments.

Best Regards,

Tingting

1 of 1 people found this helpful
• ###### 2. Re: Help me create a pie chart by combining selected slices

Hi Chen,

Each student is associated with one department only.

May be I reckon, I require a dynamic way to do this because the departments are selected by the user.

Thanks,

Rr

• ###### 3. Re: Help me create a pie chart by combining selected slices

Great, so the count will be easier in this way.

There are two ways we can do.

1st: using bullet graph with 100% reference line, and leverage on table calculation filter to make sure % of total is correct.

2nd: using pie chart, and with help of LOD calculation, we can find out

1. In % : count(student)/{count(student)}

2. Out %: 1- In%

where {count(student)} finds out the total students regardless of the filter applied.

Attached is the sample workbook, I used sales instead of student, but the logic is the same.

• ###### 4. Re: Help me create a pie chart by combining selected slices

You can try something like that.

Go through the workbook and let me know If you've any query.

Mahfooj

• ###### 5. Re: Help me create a pie chart by combining selected slices

Hi Chen,

For some reason, I am not able to unpackage/open the workbook. I am not sure.

hmm... are these the only calculations used ? .. I mean do I need any other calculations to achieve this ?

I am using version 9.1

Thanks,

Rr

• ###### 6. Re: Help me create a pie chart by combining selected slices

Hi Khan,

I am even not able to open your forwarded workbook. I am not sure why ?

Can you please explain me how you accessed the selected departments from the quick filter ?

Thanks,

Rr

• ###### 7. Re: Help me create a pie chart by combining selected slices

Lets assume you've data like this

For selected department you've create a calculated field like this

SUM({INCLUDE [Dept]:SUM([Students])})

and for non selected department

SUM({sum([Students])})-SUM({INCLUDE [Dept]: sum([Students])})

Once done with calculation now create a dummy measure

Then drag the dummy measures to rows shelf and from marks card change it to pie chart. Put quick filter on Dept dimension.

Drag Measure Names to filters shelf and filter only selected and non selected dept calculated fields. Drag Measure Values to Angle and Measure Names to color.

you can see the Pie with selected and non selected. Hope this help.

Mahfooj

• ###### 8. Re: Help me create a pie chart by combining selected slices

Hi Rakesh,

Thanks

• ###### 9. Re: Help me create a pie chart by combining selected slices

I was using Tableau 9.3

Basically Tableau has an order to filter out those data, details can be found at Tableau's Order of Operations

For method 1. Writing a table calculation to get the subcategory, and moving it to filter will make sure the % of total is correct. Make sure the calculating method is correct.

The 22% and 100% are done by reference lines.

Personally I like bullet graph better than pie chart, cos angles can be misleading sometimes.

Hope this helps.

• ###### 10. Re: Help me create a pie chart by combining selected slices

Hi Bale,

hmmm... yeah I have gone through it...Yeah the url is good but there is not much about dynamic selection of dimensions and percentage to total display of selected/unselected slices.

Thanks,

Rr

• ###### 11. Re: Help me create a pie chart by combining selected slices

Hi Chen,

While opening the twbx, I am getting this error "Unable to establish connection: The required data source type has not been licensed.". May be due to the fact that my company's licensed version will not accept ms excel format.

I know about the shortcomings of a pie chart but for some reason, I need to do this using pie chart only.

Can you please elaborate on the calculation part so that I can get a more clear picture and implement the same.

One more thing, like I have tried the below calculation fields :

1. Name : 1InValue

Value : SUM([Student Id])/sum({SUM([Student Id])})

2. Name : 1OutValue

Value : 1 - [1InValue]

and when used them, the chart was fine for single and mutliple selections but the main problem was when I select 'All' in Departments, the chart is not displaying 100% but instead it's displaying as below :

for '1InValue' its displaying 66.81%

for '1OutValue' its displaying 33.19%

Rr

1 of 1 people found this helpful
• ###### 12. Re: Help me create a pie chart by combining selected slices

Hi Rrakesh,

For student ID, may I know what is the column member looks like?

If student ID is something like 1100394, 1146403, 463667, maybe we should convert the aggregation method to countd(student ID).

I have extracted the data to tde file, and you should be able to open it.

Best Regards,

Tingting

1 of 1 people found this helpful
• ###### 13. Re: Help me create a pie chart by combining selected slices

Hi Chen,

Yeah you are right regarding the 'Student ID'...I used COUNTD instead of SUM.

But even though my calculations and setup looks same as yours, my workbook's pie chart shows exactly two slices when all the departments are selected. When it should actually show single full pie indicating all departments. Please check the attached screen shot (selectAllDepts.jpg).

Also, the percentage of selected departments (when all departments are selected) is 100.019. I have actually added 'Measure Values' to Labels and formatted the numbers to 'percentage'. Just toggling the formats to get the required look.

Please guide me if I am in the wrong direction.

Rr

• ###### 14. Re: Help me create a pie chart by combining selected slices

Hey Chin,

Sorry for the delayed response. I am even working on some other tasks and tried for some time.

Your calculation "SUM([Student Id)/sum({SUM([Student Id])})" really helped when I used the 'Student Id' field.

But when I am trying to filter out results using 'Student Name' field (as quick filter), I cannot use SUM. Hence I tried using COUNT which is showing a pie chart as seen in the attachment.

Please help me in figuring out the correct aggregation when using the descriptive field (such as 'Student Name' here).

Hope I am clear,

Thanks,

Rr

1 2 Previous Next