How make a table groups data into percent-of-total ranges

[bhamp22]

Hello,

For a report that shows how many times faculty have clicked on links in a newsletter, I create a table that shows the number of faculty who are within certain percentile ranges (as shown below).

I pull the basic data from Power BI that involve faculty names, the amount they click in a given month, and the percentage they actually clicked over total possible clicks for that faculty. This percentage is what determines where the faculty will fit within the range. A sample list (minus the names) is given below. For this data, say the total amount possible is 65.

So, my question is how can I group the faculty percentages into the ranges as shown in the first table so I can skip exporting the table and doing the step in excel. Is there a way to do this in Power BI? Thanks in advance for the help!

[bhamp22]

The Source data has the following columns:

Full Name: Name of the Faculty who clicked

Date: Date of the Click

Unique Value: Combination of Full Name and Date in order to have a unique value to count by.

These are the only columns that play into the visualization. I usually just use a count of the full name without a measure in order to get the count for each faculty.

Is there anything else you would need?

[tamerj1]

@bhamp22 

This is visual that you want to build

But i guess you are also interested in utilizing a date slicer to filter the results dynamically based on the selected date range. Am I right? Or the date has nothing to do with your visual?

[bhamp22]

The date is applied as a filter to all pages. So, the date doesn't specifically go with the visual, but it is filtering out data.

[tamerj1]

We can create a calculated column that calculates the percentage buckets. The resulted column can be used in the rows of your table or matrix visual. But this shall be based on the percentages as per the source data without applying any filter. For example if we consider the following is your source data without applying any filter then the percentages that you see below shall be fixed regardless of the date selection. In other words, each row in the source will have a fixed percentage and hence will be assigned to a fixed percentage range. However, once your visual is set then when applying a filter on the date, the values will change because some faculties might not have any click in the selected date/date range. But this might not be what you want?

 

[bhamp22]

That is what I want. The data filter is the past year. So the percentage in the end would reflect only the percentage within that year. So basically, have the visual in the buckets, and then apply a date filter afterwards that will limit the data to the past year. Does that make sense?

[tamerj1]

We can apply the buckets segregated by year if that what you want. The it should be doable. You can then select the year and you will get tahr data buckets based on the selected year. Is that ok?

[bhamp22]

I would prefer to have it based on the date range in the filter applied to all pages and not just by year.

[tamerj1]

I was about to say this is not possible. Then I had an idea. I will give it a try. This requires some sample to work with. Please share a sample of your data and hopefully will get back to you by tomorrow morning either with happy face or sad face. 

[bhamp22]

Full Name                                                                           Date            

Doe, John	3/30/222
Nancy, Susie	3/30/222
Lance, Jacob	3/30/222
Names, Boy	3/30/222
Bobby, Noah	3/30/222
James, Oliver	3/30/222
Clark, Daniel	2/15/2022
Doe, John	2/15/2022
Nancy, Susie	2/15/2022
Lance, Jacob	12/15/201
Names, Boy	2/15/2022
Bobby, Noah	2/15/2022
James, Oliver	6
Clark, Daniel	6
Doe, John	4
Nancy, Susie	9
Lance, Jacob	1
Names, Boy	1
Bobby, Noah	16
James, Oliver	39
Clark, Daniel	1
Doe, John	1
Nancy, Susie
Lance, Jacob
Names, Boy
Bobby, Noah
James, Oliver
Clark, Daniel

I do not have time to finish a sample, but place dates in for the rest of the names.

[tamerj1]

@bhamp22 

Yes of course it can be done. 
basically you need to create a calculated column that contains the percentage buckets and then you can use it in the visual. Can you please advise what columns are there in your source data?