
1. Re: How to calculate percentages based on distinct values within a single column, using one of the values in that column as the denominator?
swaroop.gantela Jul 15, 2016 5:32 PM (in response to Mike Sauer)Mike,
Please see if the attached could be a starting point for you.
I calculated the count of attempts using a Level of Detail calculation:
{ FIXED [Course],[Attempt]:COUNT([Student])}
Then, I calculated the percentage by dividing that count by the max count for that course:
[CountInAttempt]/{FIXED [Course]:MAX([CountInAttempt])}
Probably don't what to show the percent for attempt1, can use:
IF [Attempt]>1
THEN [Percent]
END
and place that on the columns shelf.

211273repeat.twbx 18.6 KB


2. Re: How to calculate percentages based on distinct values within a single column, using one of the values in that column as the denominator?
Mike Sauer Jul 19, 2016 10:59 AM (in response to swaroop.gantela)Hi Swaroop,
Thank you for your quick reply and solution. I'm having trouble replicating your solution in my project. Is there any way you can attach the tableau file so I can see exactly how you're doing that? Sorry if it should be obvious from your text and screenshot...
Many thanks,
Mike

3. Re: How to calculate percentages based on distinct values within a single column, using one of the values in that column as the denominator?
swaroop.gantela Jul 19, 2016 11:14 AM (in response to Mike Sauer)Mike,
The forum message entry: Re: How to calculate percentages based on distinct values within a single column, using one of the values in that column as the denominator?
should have the downloadable twbx for you: 211273repeat.twbx. It should be in v9.0. Which version are you using?
I've reattached it below.
Happy to help further as needed.

211273repeat.twbx 18.6 KB


4. Re: How to calculate percentages based on distinct values within a single column, using one of the values in that column as the denominator?
Mike Sauer Jul 19, 2016 12:35 PM (in response to swaroop.gantela)Oh! For some reason I only saw the screenshot...Thanks for your reply. I will look at this again soon and see how it works.
Best,
Mike

5. Re: How to calculate percentages based on distinct values within a single column, using one of the values in that column as the denominator?
Mike Sauer Jul 21, 2016 6:15 AM (in response to Mike Sauer)Swaroop,
I still can't get my view to generate a bar graph like you did. Below is a screenshot of what my view looks like after replicating your steps. The one thing I see that's different is the pill type of the SUM(CountInAttempt) on the row shelf. In your view, it's a blue dimension, while in mine it's a green measure pill. I tried changing the data type and moving it around between Dimensions and Measures, but can't seem to make it change from green to blue. I also include here a screenshot of the 'Attempt' column properties for you to compare (they are not the same in your data and mine, and may be what's causing my issue). Another thing I wonder about is my calculation for the CountInAttempt field. Here is my calculation: { FIXED [COURSE], [ATTEMPT]:COUNT([Number of Records])}. I used COUNT([Number of Records]) because we try to avoid using the student ID. However, to test whether that would make a difference, I changed the formula to count the ID ( which would be your 'Student' column), but it made no difference. However, those column properties also differ between your data and mine. I attached a screenshot of the properties of the ID field as well for you to see.
Do you have any other suggestions or thoughts?
Thank you,
Mike

6. Re: How to calculate percentages based on distinct values within a single column, using one of the values in that column as the denominator?
swaroop.gantela Jul 21, 2016 7:36 AM (in response to Mike Sauer)Mike,
I don't think it's too far away.
With respect to the pill type, I had made the Sum(CountInAttempt) discrete.
This is done by rightclicking on on the pill as it sits on the Row shelf and
selecting "Discrete" (please see below image1).
Here is a great discussion on pills and the significance of their colors and
discrete/continuous:
Blue things and Green things  The Information Lab
I noticed that your Mark Type was set to "Automatic", but you can force it to
be Bar. (please see below image1)
With respect to the data types of the different fields, I don't think it will affect
it much, but you can right click on a field and Change Data Type there
(please see below image2). If you would like and if there is nothing proprietary to show,
please attach a screen shot also showing the far left panes with all the lists of Dimensions
and Measures. The icon next to them tells the data type.
With respect to the Number of Records, I think it should produce the same result.
Image1
Image2

7. Re: How to calculate percentages based on distinct values within a single column, using one of the values in that column as the denominator?
Mike Sauer Jul 25, 2016 12:44 PM (in response to swaroop.gantela)Swaroop,
Thanks for the additional tips and help. It's still not looking right, as now I'm ending up with percentages that don't look right to me. I've created a sample data set with just the bare bones fields (attempt and course) and tried to recreate your view with that data and attached it here for you to see. If you have the inclination to look at it, I'll appreciate whatever additional help you can provide. For example, looking at the first course (BUSA100), I expect the percent for attempt 2 to be about 14% (376/2634), but it shows 54%. Also, I'm not sure what to make of the values of your Show Percent axis with the scale going from 02.3. Can you explain what's going on there? Thanks again for all your kind attention to my challenge.

swaroop.twbx 19.8 KB


8. Re: How to calculate percentages based on distinct values within a single column, using one of the values in that column as the denominator?
swaroop.gantela Jul 25, 2016 2:37 PM (in response to Mike Sauer)Mike,
Apologies about that, I think the ShowPercent was working for a different setup.
Instead, you can try:
If [Percent]<1 then [Percent] end
I think that will produce the correct numbers.

211273percent2.twbx 40.4 KB


9. Re: How to calculate percentages based on distinct values within a single column, using one of the values in that column as the denominator?
Mike Sauer Aug 8, 2016 11:43 AM (in response to swaroop.gantela)Swaroop,
Thanks for your reply. After making that additional tweak, it still wasn't working as i expected, so I'm abandoning this endeavor and finding different ways to present the data. I really appreciate your taking time to try to help a complete stranger. It's very kind of you.
Best wishes,
Mike