Forum Discussion
Solved
Query to return results based on highest 80% of column value
Afternoon All, I'm stuck on how to tackle a problem i've been presented. My source table contains rows of hypothetical opportunties that have been asigned a Most Likely Saving (if they were to be...
davehus
Memorable Member
Memorable MemberHi BenTen22 ,
Welcome to the world of power query. 🙂
Can you provide some dummy data and indicate on which column that you'd like to get your 80% calculated on?
Thanks
Hi davehus
Some example dummy data now included. Having scrubbed the source data i'm left with the example below. I'm then looking to return the same information but filtered/sorted to only show rows that fall in the top 80% of the total value of the Most Likely Saving Column. Doing this manually in excel I would expect the returned table to only show 1-7/8(depending on rounding). I've been playing around further and might be creating a circular reference. Also trying to make other suggestions posted here work having no luck at the minute.
Cheers
| ID | Name | Detail | Project | Valid | Date Created | Cost Category | BRAG | Minimum Saving | Most Likely Saving | Maximum Saving | Confidence |
| 1 | Name 1 | Detail of Idea 1 | 1 | Yes | 01/04/2022 | Cost reduction | Amber | £10,000,000.00 | £60,000,000.00 | £ 80,000,000.00 | 10% |
| 2 | Name 2 | Detail of Idea 2 | 2 | Yes | 05/05/2022 | Cost reduction | Amber | £30,000,000.00 | £59,000,000.00 | £100,000,000.00 | 30% |
| 3 | Name 3 | Detail of Idea 3 | 1 | Yes | 23/05/2022 | Cost reduction | Green | £40,000,000.00 | £50,000,000.00 | £60,000,000.00 | 90% |
| 4 | Name 4 | Detail of Idea 4 | 3 | Yes | 01/04/2022 | Cost reduction | Amber | £26,997,546.00 | £40,492,405.00 | £53,989,873.00 | 10% |
| 5 | Name 5 | Detail of Idea 5 | 1 | Yes | 11/05/2022 | Cost reduction | Amber | £12,000,000.00 | £24,605,136.00 | £35,527,078.00 | 80% |
| 6 | Name 6 | Detail of Idea 6 | 4 | Yes | 24/05/2022 | Cost reduction | Red | £18,000,000.00 | £20,000,000.00 | £22,000,000.00 | 10% |
| 7 | Name 7 | Detail of Idea 7 | 4 | Yes | 24/05/2022 | Cost reduction | Red | £15,000,000.00 | £20,000,000.00 | £25,000,000.00 | 25% |
| 8 | Name 8 | Detail of Idea 8 | 2 | Yes | 01/04/2022 | Cost reduction | Green | £5,000,000.00 | £15,908,552.00 | £22,970,179.00 | 10% |
| 9 | Name 9 | Detail of Idea 9 | 1 | Yes | 25/04/2022 | Cost reduction | Green | £2,000,000.00 | £12,000,000.00 | £16,000,000.00 | 80% |
| 10 | Name 10 | Detail of Idea 10 | 5 | Yes | 24/02/2022 | Cost reduction | Green | £10,000,000.00 | £12,000,000.00 | £15,000,000.00 | 10% |
| 11 | Name 11 | Detail of Idea 11 | 1 | Yes | 11/06/2022 | Cost reduction | Amber | £2,000,000.00 | £10,000,000.00 | £11,000,000.00 | 10% |
| 12 | Name 12 | Detail of Idea 12 | 3 | Yes | 24/02/2022 | Cost reduction | Amber | £10,000,000.00 | £10,000,000.00 | £15,000,000.00 | 40% |
| 13 | Name 13 | Detail of Idea 13 | 4 | Yes | 24/02/2022 | Cost reduction | Green | £10,000,000.00 | £10,000,000.00 | £15,000,000.00 | 90% |
| 14 | Name 14 | Detail of Idea 14 | 2 | Yes | 15/11/2021 | Cost reduction | Amber | £8,000,000.00 | £9,486,312.00 | £13,697,178.00 | 50% |