12 Replies Latest reply on Aug 23, 2018 11:27 AM by Deepak Rai

# Calculate Percentage of One Measure

My data set captures phone calls that were conducted by my organization to the end user. These calls were recorded and each one was coded for certain themes that presented (technical issues, negative feedback, etc.). Ideally, I want to create a pie chart breaking down the weight of each issue faced (e.g. 34% of calls had a Technical Difficulty issue). I'm having trouble calculating the percentages correctly.

For each theme, I created a measure that looks like this (had to distinct count the record ID as there are duplicates):

COUNTD(IF [Lack of time]='Yes' THEN [Record ID] ELSE NULL END)

So far, I've been able to create a horizontal bar chart looking like this:

However, when I try to calculate each bar as a percentage, it goes wonky. There are 108 calls (first bar Distinct count of calls) and each measure below is a theme. I'd like each bar to be a percentage of the total distinct count of calls (108). Once this is done, I can hopefully integrate into a pie chart for easy visualization.

Can this be done?

Thanks!

• ###### 1. Re: Calculate Percentage of One Measure

SUM({FIXED Theme: Count(No of Records)})/SUM({COUNT(Number of Records)})

I Know, Your Situation is Complex than This, But you need to post some fake data set or Workbook so that we can Help you. So Try modifying above, else post some dataset.

Thanks

Deepak

• ###### 2. Re: Calculate Percentage of One Measure

I think you will need to create a table calculation.  There is an option if you click on the 'measure value' drop down options from the Column Pill and select 'quick table calculation' > 'percent of total' that would break it down by percentages.  So for example, the 'calls with barriers or outcomes' would be the largest percentage since there are ~93 or so of the 108 calls and that would be relative to all the other codes.

Since the codes are used multiple times on the same call, a calculation would be needed where the 'distinct count' would equate to 100%.  That might be where my skill set ends without playing more with the data.  Hoping someone else who is better at calcs can advise how to build the calculation so each code is it's own distinct percent of 108.

• ###### 3. Re: Calculate Percentage of One Measure

Is the data shaped as one row per call with something like columns [call id] and [theme]? If yes, then the pie chart in Tableau should work out of the box.

If you have one column per theme with a yes/no value you could create a calculated field named [call theme] to combine them and use a formula

IF [lack of time] = 'Yes' then "Lack of Time"

ELSEIF [technical issues] = 'Yes' then "Technical Issues"

That combines all columns into one theme column, assuming there can be only one theme per call.  The data would look something like

call id | lack of time theme | technical difficulty theme | call theme

1 | yes | no | lack of time

2 | no | yes | technical difficulty

You would click on the [call theme] dimension pill, then the [Number of Records] measure pill, then click "Show Me" and click the Pie Chart to have Tableau build it for you.

• ###### 4. Re: Calculate Percentage of One Measure

Hi Aaron,

Thanks for the response. I really like this suggestion as it seems the simplest. However, each call can have multiple themes, so unfortunately it won't capture this.

My data is structured as you say, with each row being a distinct call and the columns representing the themes (among other things).

Do you know of a different calculation I can make which allows for multiple themes per call?

Mariah

• ###### 5. Re: Calculate Percentage of One Measure

Hi Mariah,

Darn!  The problem here is that a pie chart in Tableau needs to count records so it can draw the angles correctly.  Can you use Tableau Prep to pivot the data into a row-based set and use that as your data source?

The other argument to make is that pie charts are not the best way to visualize data, and your bar graph is actually the best way to do it!  You could keep your bar chart and add the % of Total as a tooltip or the label.  To do that, you should be able to add # of Records into the tooltip or text area and set as a quick table calculation:

• ###### 6. Re: Calculate Percentage of One Measure

This was my first instinct: to just demonstrate the percentages in the bar graph.

I just couldn't get that to work - no matter how I compute it, (as % of total, table across, table down, etc.) the totals are either 1% or 100%. And never as a percentage of the distinct count of calls.

I'm not sure how to wrangle my data or what I'm doing wrong to make this work!

• ###### 7. Re: Calculate Percentage of One Measure

Hi Mariah,

This is doable and You could have got it answered in few Seconds if you had attached a packaged workbook with fake data. Please Create one with fake data and you will see it cracking.

Thanks

Deepak

• ###### 8. Re: Calculate Percentage of One Measure

Attached is a de-identified subset of my dataset.
As you can see there are many duplicates, they are joined on variable 'Wav Link'. Each row is a call and the themes are organized in columns.

• ###### 9. Re: Calculate Percentage of One Measure

I am assuming the bar charts are using Measure Names and Measure Values since you have each theme column as a row?

LOD calcs should do it then I think...

Calculated Field: Total Calls

{ FIXED : COUNT([Record ID]) }

Calculated Field: XXX Theme Count (one per theme)

{ FIXED : SUM(IIF([theme column] = 'Yes',1,0)) }

If you wanted to break out by month/day/year at some point you would add the date dimension on the FIXED side:

{ FIXED YEAR([Attempt Date]) : SUM(IIF([theme column] = 'Yes',1,0)) }

Then another calculated field % of Total dropped into your tooltip or text for label

[XXX Theme Count] / [Total Calls]

• ###### 10. Re: Calculate Percentage of One Measure

Hi Deepak,

This worked - my only issue is that because there are duplicate records, it is over counting the occurrence of each theme.

• ###### 11. Re: Calculate Percentage of One Measure

After playing around with the data a bit more, I was able to get this to work.

Thanks so much!

• ###### 12. Re: Calculate Percentage of One Measure

Good!!!  See, Magic Happened after you attached a Data set