Calculate the percentage of people who exceeded the weekly target.
I need to add a calculated field to know the percentage of people who exceeded the weekly target. And as each week varies the target also know if it can be conditioned depending on the target of each week.
Example, I have 11 people and the target is an accuracy of 97.69%, then I want that all those who are equal or greater than that percentage is divided by the total number of people.
Hi @LFZeledon ,
I used the countif() function in a calculated field, percent_employees, as follows:
countIf({Employee ID}, {Monthly Compensation} >= 14000 ) / count({Employee ID})
Then I formatted this as a percentage to show two decimal places and displayed it in a pivot table:
That what you’re looking for?
ws
Hello, thank you for your reply. I tried with the formula you indicated but it does not give me the correct percentage. I need to go week by week calculating the percentage of people that exceed the indicated metric. My idea is to put a table for each week where only the percentage is reflected.
This is an example, I have this table that I will make for each week. My idea is to put at the top in a separate field the percentage. In this specific case for example, those that were above or equal to 97.69% (0.9769).
Hi @LFZeledon ,
I see. So you have to recalculate the threshold percentage every week? Using a separate table is straightforward approach.
A couple thoughts that might help:
A weekly aggregation on the pivot table could accomplish this without the separate table. Just add a date to the table and aggregate by week
A way to create a separate table, without actually creating a separate table is to duplicate the dataset and only use the two or three field that you need to calculate the the percentages. Then you could create the simple percentages as calculated fields on the dataset. (it’s a thought).
Hope that helps. Let me know what works for you, I’m curious how it ends up.
ws
Thank you very much. I don’t know how noticeable it is, but I’m a newbie at this.
I think I understand what you are telling me about adding the date and doing the aggregation by weeks, but I already have a field that tells me the weeks.
In any case, I think that would be useful to have all the weeks together in the same pivot. But leaving that aside, my main interest is: in the general page of my dashboard, to place as informative fields (one for each week that indicates only the percentage of people who achieved the grade. For example with the above screenshot it would be % of 45%.
I imagine a formula that depending on the week (from 43 to 52) and according to the metric for each week, sum up the daily performance of each person for that week and divide it among the people.
what I am looking for is a field like this, I made it with the first formula you indicated, but it does not calculate it correctly.
Thank you very much for the help given.
Hey @LFZeledon !
I did not notice, but welcome to the party!! One thing about analytics visualizations is that there are always several ways to solve a problem. It’s as much about the art & craft as it is about the science.
If you’re comfortable, it’ll help me to know more details. If you have a (safe and CISO approved) screen shot of the table that you’re using along with the visualizations that you’re designing, I can help you tweak those.
I do have a question on how you’re selecting the week? Are you displaying all weeks in a table, along with the threshold percentage? Is the week selectable as part of the dashboard? Or does the week default to ‘this’ week. In the KPI that you showed, how did we get to week 43?
Thanks!
ws
Hello @LFZeledon, just checking in since we have not heard back from you. In order to best assist you in finding the solution to your QuickSight problem, can you respond with the information requested by @wstevens01? Thank you!
Hello @LFZeledon, since we have not heard back with the information that was requested above, I am going to archive this topic. If you still need assistance with this issue, please post a new question and link this topic to ensure a QuickSight expert can quickly assist you and find a solution for the problem you are facing. Thank you!
