8 Replies Latest reply on Aug 31, 2017 4:17 AM by Richard Silvekroon

# Getting a count and presenting as percentage

I'm a total newbie regarding Tableau, but I'm in need to make some adjustment to some data and in some hurry so hoping someone might lend their assistance. =)

I have data that presents grades for students, but these are totals and I want to present them as a percentage (to answer the following question: "what is the percentage of students receiving a certain grade?"). As the data is located behind SQL servers I don't have access to I'm unable to upload a packaged workbook but will try to show with screenshorts. I have the scaled the report it down and this is what is relevant for this question:

As you can see the grand total count is off since one student can have several different grades in a number of courses. The grand total only counts the amount of users not the amount of grades. This is why when I convert the data to percentage it's wrong:

The amount of users are calculated like this: COUNTD([PersonID]) where the PersonID is a dimension with an ID for every user. The different grades are as you can see in the screenshot listed in a dimension.

To sum up, what I want to do is to show the percentage distribution between the different grade levels.  In screenshot 1 the grand total of grades is 273 (excluding null values). Therefore the percentage of grade level "D" with is 19% but I have yet to find a way to calculate that in Tableau. Any tips?

• ###### 1. Re: Getting a count and presenting as percentage

Try This:

{FIXED [Organization],[Grade] : SUM(IF Grade<>Null THEN [Number of Users] END)} / {FIXED [Organization] : SUM(IF Grade<>Null THEN [Number of Users] END)}

• ###### 2. Re: Getting a count and presenting as percentage

Thank you for a quick response. With that calculation I get this error: "Cannot mix aggregate and non-aggregate comparisons or results in IF expressions."

To clarify: The measure "Number of users" is an aggregate and both "Organisation" and "Grade" are dimensions.

• ###### 3. Re: Getting a count and presenting as percentage

{FIXED [Organization],[Grade] : (IF Grade<>Null THEN [Number of Users] END)} / {FIXED [Organization] : (IF Grade<>Null THEN [Number of Users] END)}

• ###### 4. Re: Getting a count and presenting as percentage

The IF expression is still being used in those calculation, and since both "grade" and "organisation" are dimensions they can't be used together with "Number of users" (measure). I tried using COUNTD on the grade dimension to create a measure of it but that only returns a value of 1 since the true amount is located in "Number of users".

• ###### 5. Re: Getting a count and presenting as percentage

Sorry, It is difficult to help without some packaged WB. I think You would appreciate That.

• ###### 6. Re: Getting a count and presenting as percentage

I think I managed to extract the data and I have attached the packaged WB to the original post. I appreciate your assistance!

• ###### 7. Re: Getting a count and presenting as percentage

So , This is what you need

I used 2 LODs to get this:

and Percent is:

It was Easy. Isn't.

Thanks

Deepak

1 of 1 people found this helpful
• ###### 8. Re: Getting a count and presenting as percentage

Many thanks for the help, it was exactly what I was looking for!

Regards,

Richard