Query to return results based on highest 80% of column value

[BenTen22]

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 realised). My query already filters for Opps that are still to be realised but Im looking to further reduce the number of rows by only displaying the Opps that sit within the top 80% of the total ML Value. Just starting out on the Power Query journey and may have bitten off more than I can chew on this one. 

let
    Source = Excel.CurrentWorkbook(){[Name="Table_query__18"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name of efficiency / initiative", type text}, {"Detail of the idea", type text}, {"Project", type text}, {"Valid", type text}, {"Date Created", type datetime}, {"Opportunity type", type text}, {"Cost category", type text}, {"Idea Owner", type text}, {"Asset Type", type text}, {"Function", type text}, {"Discipline", type text}, {"BRAG Status", type text}, {"Fish bone category", type text}, {"McKinsey Theme", type text}, {"Speed Theme", type text}, {"Minimum saving obtainable", type number}, {"Most Likely Saving", Int64.Type}, {"Maximum saving obtainable", type number}, {"Target Implementation Date", type datetime}, {"Confidence Level", type number}, {"Saving already delivered from the efficiency", Int64.Type}, {"Target Cost to Implement", Int64.Type}, {"Actual Cost to Implement", Int64.Type}, {"Actual Implementation Date", type datetime}, {"Comments on current position providing some narrative of next steps (SharePoint to generate date of update)", type text}, {"Has a A3 / Business case been completed?", type text}, {"Link to Supporting Document (A3 / Business case etc)", type text}, {"Most likely completion date?", type any}, {"Sustainability Benefit", type text}, {"CO2 Saving", type any}, {"Delivery Unit", type text}, {"Person who generated the idea", type text}, {"Modified By", type text}, {"Modified", type datetime}, {"Created By", type text}, {"Legacy ID", type text}, {"Item Type", type text}, {"Path", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Valid] = "Yes") and ([BRAG Status] <> "Blue") and ([Cost category] = "Cost reduction")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Most Likely Saving", Order.Descending}})
in
    #"Sorted Rows"

[v-yalanwu-msft]

Hi, @BenTen22 ;

You could create a custom column.

= Table.SelectRows(#"Sorted Rows", each [value] >= List.Max(#"Sorted Rows"[value])*0.2)

Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZjBbuMwDER/pci5QE1RsqS9tXveLyj6/7+x2STrkcmZBAhyIvw8FGck+fv7Yh/2UTYbb1Z+bdv19/b55/J+2bfr3+fl5/1fRWEVNq9/X48KZxXVrn+/bxVFUA5I4ZDSD0jhkNYOiHOIgeJCyjgoLqSMg1JfSakcsh+Myhn9QDSOqOVgNCEE7Woc4gdkF91qB2TnED8YO2cYmtU5pNkB6RxSoaQLCqZrCCmgDCEFlCEoaNjklAKnTE7BdE0OKfOA2MYpjvm6lvCWwY+b4GDETPjegRG+xxzba9+bMP4yyias75gzU96vAN05Mz4EI/CQEytaiTGWKpAwhVNutjvlWHoGulZeUlxQasyxWFFBcU5xUCqnVExA5ZQlLavQggFoYl1AaZxSMM6NU2yLWZYomLJdUCyGWeoHvNk5ZcYso+NxyjLa0lOWpReF/wen3HbsU5alZ8Qoo9NxirJUkbIsQZaM2YSWFGWpYvH+JtZlCRlh/gbDmHD/0jQT9q9omwn/o20m/L9Ms4kAWGbgrqdsOmbuclLFEgB3NakiZll+BBKzcEiJWZafAc84p3g8k2WxyGUXlJhlWSwGoAoKDrGVUwzL3wQFWhqneMyyrCWeyzIFx4xdLH7Msmcd64ICb3ZOQQB00bB4LssQhP/gkAbKEHOMmJnCLTHMUgXWfnKILxmzCS3LAWMT69JimPGOnMIsv8tifuV+dM2E/W/ePYUZXeBTmOVXWY5MIgHKIuiOsZdhlipSmMWKEi+Y+RlQUzilYQconOIpzdJ7wDUutIDinLKMQBUUHDQqpyy7WRVacGZqnOKgNE7JaZbUYl12oQXrsnNKBWUXFERAFxQkcxdaMGNdzBjuGINTKnaZIVYfhhmcghGbHNJglykgODNNDqlY/P9xljCL+zfBSffM3LN0z0wlC0fYv6aLZi5JYUZf9pxmT0bAVAJg33zIKdqbDzmxYpnnh5pY4eha4ZSa0ixWNOxphVOWu5lzypLMzimOk4ZzimGfqZzi6WyW1MYwS1JilqVVQb+aUBI/mKUKCNk5BN3aOWP5hrlzRokfzHI/4wezrAT275xSIWWIfsVL5pM1GWJN0rksVaRz2RMpU1AeSfbzFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, value = _t, name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"value", Int64.Type}, {"name", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"value", Order.Descending}}),
    #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each [value] >= List.Max(#"Sorted Rows"[value])*0.2)
in
    #"Filtered Rows"

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KT_Bsmart2gethe]

Hi @BenTen22 ,

If I understand the question correctly, you can add a custom step:

EightyPercent = Table.FirstN(
                      #"SortedRow",
                      Number.RoundUp(
                             Table.RowCount(#"SortedRow") * 0.8
                      )
                 ),

Regards

KT

[davehus]

Hi @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

[BenTen22]

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%

[davehus · Accepted solution]

Hi @BenTen22 ,

My approach is a little different in that I've used a running total rather than top n. It gets you to 77.5% of the total for your records. See attached pbix file.

Did I help you today? Please accept my solution and hit the Kudos button.

[BenTen22]

@davehus this does the trick, thankyou!