Group and bucket

[Anonymous]

New to Dax and wondered if anyone can solve this issue fro me please

I have a table (simplified version below). that hold a list of People, relevant dates and some results, potential and actual.

TABLE1
Person	Date	Potential Result	Actual Result
Person 1	01/11/2020	10	10
Person 1	02/11/2020	9	8
Person 1	16/11/2020	8	8
Person 1	21/11/2020	10	9
Person 1	24/11/2020	6	5
Person 1	19/11/2020	14	10
Person 1	01/10/2020	23	20
Person 1	12/10/2020	16	15
Person2	01/11/2020	12	12
Person2	02/11/2020	13	12
Person2	16/11/2020	15	10
Person2	21/11/2020	16	16
Person2	24/09/2020	12	10
Person2	19/10/2020	14	12
Person2	01/09/2020	16	15
Person2	12/10/2020	15	14

There is a slicer, that will determine the time range i'm interested in, assumeing the slicer is set to November 2020, then i ned to aggregate the data to determine the total results for each person in the list and work out the ratio of Actual Result against Potential result as show below

TABLE2
Person	Count of Employee	Ratio	Potential Result	Actual Result
Person 1	1	88%	57	50
Person 2	1	89%	56	50
Person 3	1	77%	57	44
Person 4	1	88%	48	42
Person 5	1	95%	44	42
Person 6	1	84%	50	42
Person 7	1	69%	59	41
Person 8	1	91%	43	39
Person 9	1	95%	40	38
Person 10	1	95%	40	38

I am then lookng to count up the number of occurrances in each band, like show below

Count
0-70%	71-80%	81-90%	91-100%
1	1	4	4

Then show as percentage of the line total

Percentage
10%	10%	40%	40%

Is this possible to do in dax? It's the initial slicer with the dynamic timeline that is giving me a headache.

Any help would be appraciated

[Anonymous]

Thanks for the reply, so easy when you know how. Appreciate your time

[Fowmy · Accepted solution]

@Anonymous 

Please check the attached file. hope the file gives you what you expected.

You can download the file: HERE

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn