Forum Discussion
Group and bucket
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
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 🙂
2 Replies
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 🙂