Forum Discussion

Einomi's avatar
Einomi
Icon for Helper V rankHelper V
2 years ago
Solved

Keep First 80%

Hi,   I am responsible for auditing, monitoring and reducing costs of our suppliers. We got fruit and vegs from the market. I have managed to get a daily .csv report of what we buy. So I have combi...
General Comment
Need Help
Show and Tell
Tutorial Requests
  • lbendlin's avatar
    lbendlin
    2 years ago

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZhbk6I4FID/Smqedqu2ZxDx9hgwItXcluD0dM3OA21nlRoEF3Vmen/9JiGJoQXc0uouPZ8nJznX8PXrBxjHPsIPdgKRvUlCDD6Cjx/++GBa9A88Hgty+vDtD4W50Id3iMhfolAwE7ObSWAYPks9k04m9sJH4MOlxMbGO2xjo8T1QiQtno8YcHkh9S4vb6Hl5bzdA5OvZv0fdGywFS3DGmRRuSvy0x44I7WVXhYfs5KxAUPnsw72c+TAZaR8MObH96PaZq9VQ9jw0QtdEEcpTCPwyzIEOmMrx9U5O1dEkiF9ST/M6R87K+lLSBEM8cOKmiUIZr5NslKKUZpEUSqF4xkXk3NdVWeB+BtEd5Z4ygHmbMqo4kLopupc2pHAwEfPoHGrQBdtXzrQtqGLwDqrX8HIoO/vO6ZwwaxyspeXbNdFmgZ9c3LcxyXktdmB0YHE2HM2AQYPwE2QilmLkTE5HkndBzYBct3zMC3d3pykNcAH3he0lGbc5a5W8NwYhltGLAbwpKXZmpt3WJkAzWncgXUrTGvI6mfk+9GTftKToZMWuG7MkBsF3vbMrf4koSnwAGyEZSaYxpTHUV3LPFCUs4ZhikKoCtYNiXyUeNCRqkY8uklB6jzbasiz2saIHf50oTDSTVkifzn0piNyg1zRqE+Roizu8XvUtGO5tef7nlZKnH1eFEoBl/K6f02zyaIXS1QSjCbWDbXPf5CTlBuN+IdSEXnAjh5bllQ5sKvvjdxHAQpTrWvw3TgFOZDyrOqwU1GXlK+klrYyRn7XEBGtfDBcokQivFZTqE1tEhelqVptMuKnW13qHTmf5WobJedF6rK9HF5UEDLhxPg0NcBykzprFTzmEIpC1/ewhOfzbnZqfJpNWmp71p9NPi3a64+6wb8uhmHO3OYfwDEMNSsmQ78JOn/TY88v2iPa4LQPtNpm9yq03p2ZafST75a+3dcSXl3OSuIyk75e0kiWtjAPss+NIIFuFIJVsvFUrWHyOttVJSdWKAyRr8lWpCxJ0ehdeS7WRfmu+d6Fie/JisO7uJvVRS7KDR1LXBXBLB/dvNzJTdAhLUYg/hQD24eOSimO1dmRvKf05B6P+igtt602s44SjBK41LzPiHVVn0idvdLSI0DPYYOHK3oDLwFsOW/L5o5dm1E1klET4y5mioPopFQF5Mqm97BFl2GP3pPX8jJf8jH/mQt5tPYTaOu19LHaF3X2IgAfoUes1SWfkO+6xK5+NXE37xCG5Hz1oi4MIum6qcFFh6rUZdSBuHU14ASg7jt1YTj1nEeG8onglk3TjYMenEiP2dYx+c/OGsnZ1GRi/227JyICAhi6EfjNC+LfteANsnJXSSDxMIi9mEa3mJWv59kakwPkRyGf1aDvQS1nA1LII1DME81rmS581tchb9leajy5mcmDvDxrlY19bL7f4DUdt+loYpPTGUyKlxNvbnxCCC6nPR27D6d3aFyTY7b9LnJpdkOGcAmdKFXVhOkKs9dsW53FQBIiJ6UHdW2GLKRDsj1ntWqFUeixvbMWTG8H4BcwAE84Fl1Rmcvd32AjMOJ2LTq5JnUpNaNvPsHznBoA5/TNQWuQMy36bq4EwwoXYKFuDrdU+K8Ky1shze/8ukWL3wD6KRPwIW7yXhe9ArvXvs911DSAxbGzUvkE8ZpWy6e1l6J2aJm8O7VCK4YJZtesVZGdAaZlvCCsQFh8qM3qU0HeFBd6sUq86VgAZX6UmjD23rciHso0f6n9YFVfchFBMYKtkSAm2VaWaSpLMM3xcIUSFDpIL+qUq3Uqpg1mDYO2JkmsnzHNzVb1ifdvp6wQFTH22VAvtPPrSnERORBHAWIPG2je6l0nrg5kR09btWXG+SpxxwIp5OHSK6y9SWGoVaskO7xczlnTmllTo9MrWmmAdr1lYhEV0ghmBbubahGBYYo3AcTaIeDsfLocxJUdxwm7+rcVcffhI83XXUtXixWNkq0578b/3NBY+yiVcuifS6Y6LqZl4v1kgmmV+JsGggAS+HR9GvBFdOUp5+rsp/5A4Iblwcwns/vsXFSAW3JDRx2AQ6hCm18v8WWX1QCXmQhu/ISW19nf5Dv9SV6JkqJUpJqKV0NAtHW2Mi7dvx2I5iz+uZFEAVOA+aWQGi9vhCOuKq0OmpIb9PP1mQx/qNOHB17oARb6+i/4iff9Qucssxe89qduuU2KHb3j8KdYVi+lPZwY3YGCweXkaDa8nKKGMf0J3PjmCETBZek6PDT0V2Ze5q/st/8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Items = _t, Freq = _t, FV = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Freq", Int64.Type}},"fr-FR"),
    process = (tbl) =>
      let 
        #"Sorted Rows" = Table.Sort(tbl,{{"Freq", Order.Descending}}),
        #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
        #"Added Custom" = Table.AddColumn(#"Added Index", "First80", each try if List.Sum(List.FirstN(#"Added Index"[Freq],[Index]))/List.Sum(#"Added Index"[Freq])<.8 then true else false otherwise true)
    in
        #"Added Custom",
    #"Grouped Rows" = Table.Group(#"Changed Type", {"FV"}, {{"Rows", each process(_)}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Items", "Freq", "First80"}, {"Items", "Freq", "First80"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Rows",{{"Items", type text}, {"Freq", Int64.Type}, {"First80", type logical}})
in
   #"Changed Type1"

     

  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi Einomi, similar approach as lbendlin, but with list generate (this one should be faster)

     

    Result

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVldc6M6Ev0r1DztVs2HJPQBjxgTmwoGFuzJTd29DyRhHdc4dhbbd2b212+DJAxGdpxcUtc1VTpHLan7dLf055+fvDSNgvzLKPOC0SKLc+ur9fXT50+Ewv/QZ0wpE46NbfjX5rBew89NdVjt4dd7fV2Xu09/fW45Jl7knYend5fBSTQOYgVnpIEDkDAbYXKcXf0Y8JkXx/d6eianZ4gjQvHbs6dhfGtF3lgT2EgSOEgQ4rjkEsNiFGSTMA701jm4wSKXc+YIjIfGHx7KarnaDAnGh/3js0Ua86mioTA9R5i+m8ZG9SIokkdBHYIRIrb7Dp5gs1yvds+Wj497iojr2MS1+Tt48tdiU/PMahpHyLOlpDbpTZbvie+Nk9Yt7fooDKi/t4/F01ZiRt5tGE+sNJl788T6RZECC+UViDObInfIkm73xX5bapYYPu2Qjpq25wWjYgOfGh54cf7lBtarINho6KgsNhoQzLMkmevhtjgDKPfVdrtXoGgRwLZmYettRHCjaetDCRtZrfRqMm8WBfeW9HeFdeWJMkowd7i4GOO+Nxp5k8CaFtWThRH8/VjW07vSTQX4hCuoM1yAXzw8FEsTC0Hw17DY0hAkGOO8OaYrOLLySe4cknDCXJAbhw4j5QSe5qG/mOXWF2uSBa3mUGUEBFvzDb2jfH0tq3MkMuaOJwJ+xgVpvncy6WiRXiGjFzmYNN/VXLPwj2Csl6a2l9DmeyfHcWVKFRF25PdOot7ClO9hzOrvepuynkUgIfLsXbk97+PR+iZPTRnE7eZ7F1F3ZYSq3EGZ/K5mug+iKLnrehJTnsSY3Kb3UnUXqGNMHt31G6Wo+l7JlVde51FZBjL3xRoFuVY7giSDwzCkJM6HWckvqkqrXsvgT714HsRem6bV/lBHcGTbJvHpsARRkIWer03AZoX2y3VZrYrHDui+3cjGwbj0XYaEzYhLz1GUZgaqZgUCM/B3F6a3vZlYBQ22XYGYYzhD08QtA23ipWGQdmMx9PKLDPxK06dhFIWdNFi7HQU1dNyhFvrPq/W6nbBBNtXcUZiZXDURTCDk2oapjRRZK3+YSQEU2OWEQ6p7g+F59Xe501hkdpJmjJowCa1Rcnsx7fvP25U12v6QiCiYBfG8UzJiYw731+VLudm3tZC/Bb/cPJWV3hjzVHqUxCRQLHjxOMg06EwFBbA+bpFNgvm8NZFh5wzuUC3L/V7buGgRWnCgeCWIGLLy4fHw8tCqRA1k6BtH1ngx96dtlEqBr6tF6nLXIBTnaIJ4EoW5JnKk54PPg/ND+FzDw9E3wXrmfGBNgn1z+2vSIUEgKhAyeOOQ5N8HGC4m8sfKUy/urEyVtQJBVctNFdg5vpmR7wNr/AUFYZ9EJYiGAdGr1vgL0/42HQsyBr3LlYbQk3Mnqmapdxphg+CZSU5Wo7bYVsa8wTL2jnGjq/peZI8LHS9jEB69XnNg1iPk0MybJLF1ky3CNo2aEVWx3G4azE0Qx0F0cfRNudmUa2nNTTjJLw9eLeXIiZdFoU6lZ1qXSVGtVyqPQiM4aTXINQ9fbZZ6B6GbTwMr/ZZao8jzW21VmcDlTtMk9PZ0UhWv5Sm6m0dsGXegI/aV6E4KkRmE4MsTT5MsDzJv3Akm2l/r97Lud6bbaldWxRNkVYUM/bqvm6jir806dRw6FEobPuAIH+sGb9mHt1VGTcCQkk7wewd1+7trGMintkEUjIPnd4v0CwRttdCYIHWAynrFkLsvUriXdsFEcBvehb0AaWOqd2K3q58rBUimUeaNQnPylqu83T6vq+JBIaIguM07CbiWFhdBZWEQ8qgsf3RRo+0vqQkyFwlOT+s4OeMpLi73Xf/HDieCsoGhXdgs0W7PkWlRUfmy3XQHg9fnJ5d/yGUcC2YbsRa4/c5EkM9D/7YmUR2Vi6gNfcLA+4Ys8/nCD774SVeCmo6sdl5iD7a3d/TRvT8N9N0LMR579PvxuVTxNvPiSWL9I5yl/7woSrNis9xqSBbmVhqmoGPqbqmfzzFEGmWGJq17rTQLoiRumn0vCr1OkjijLrNyrQ+qhd5BftFaqq7lhIPJRWw47httMwlkiEJNbPNheu2bvdrsO/WBYaNggBy5yKdZkkDvOCp3e4utH3Y1AGG5TY6wEYM6rrNN0h9mh91ztd2+7E5Y0qp8LR5/KBmWO4UJd6mNULtibUSPI/bGnp/M22Spo7u3R3HxVDxu96pHjAN/Dsd8rMyNhXlcPu6Lqq3Lkzisz6VuIspiY/2ykNXEnKP817Zd4rBh65NsVvp4BhTYaqylKuIxckCCh5s2pJAJBAgE/DWXa1hVQEhQKMW6Pd+bHA78NRztDZIrOHavZyAU/uQVn7KCEJtAoLyDw7Xc3iWhZBio0pAg/l9XRiA8Hae3hWePAfLP6ngIVN3n2YxiDv8NlOw8AbGa+wL1nEEdhrHbq4aHBBkI07GFMrpfUoEoKd+ry5Q7L59CpXI3DedBP8iJqn7tjjiYojv1sry+Hb5ZF3srhzpsXcIQRvX0PWvTotqty98tMA7TVrG5fQ6xWb3qufI8PK1jmWmdKaQG2Bir+bcCB16vjRpiyuJR11QwOMshn8Q3QRbEftAtySAvCXBDCEsTRdUlSKEAnXqzvtg3SOnG58DT+xwkvpdTDde3z793xVrVFmlUX7kpK40pLF0flLilySyo37wgFXRr1CFi+1IuwWHajqMGRm0WsM9h1to3Mm82Wsy9uJOUh8vIipeHw76QTUddNvvTMLg5yeOQHR3sDvN455WhhqoY0osyFtD13X8nanJvni9mXm4u49RPXux3hxf1XJOnWf1G1J+LS4lxGQgMtwU9nTV/BdFf9ibu0ajivcYp7adcODYnA801MP1rAWH8VZvimFad//dQtB1DDonq7V4wh0T1n/pUJSTz7o7PR3+oPkPV6I78hgKZ76viZ/cxacDTyIzq121XfvTUq96mqRetHo7JufeOIcsCelErj71WhaiuePvTH5ZFZeWbQulQfheMjxdwxHy7lf8sn8p2fDBX8tpKCToLg5K9J7Hz598v5UX/bEbIscmsniRvbrxhf/R1N0bSrQhjLqFYGDi2L50pBzTfj8+T+jFUCEEgLQ2bl3NUszAOrVqpumzKh5AtXEy5O7yZPsfW5aDq1R+KUSiYDO+zZpL+rZXAyEWG6z0zdlSulysouGafjjWOS4XDxVCmzAyd1zGVGoRNGSSX4VXTJYLZh5egG/aPL6Fl+DhF94XfVnovmENc2xk2Fj0KVbzUwv/xpup8BcS0MbZz5lmv5fnr/w==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Items = _t, Freq = _t, #"Freq_%" = _t, #"Main Seller" = _t, Category = _t, FV = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Freq", Int64.Type}, {"Freq_%", Percentage.Type}}),

    Fn_Keep80 =
        (myTable as table)=>
        [ 
            // _Detail = GroupedRows{[FV="Apples"]}[Fn],
            _Detail = myTable,
            _SortedRows = Table.Sort(_Detail,{{"Freq_%", Order.Descending}}),
            _FreqBuffered = List.Buffer(_SortedRows[Freq]),
            _FreqCategory = List.Sum(_FreqBuffered),
            _LG = List.Generate(
                ()=> [x = 0, y = _FreqBuffered{x} ],
                each [y] / _FreqCategory < 0.8,
                each [x = [x]+1, y = [y] + _FreqBuffered{x} ]
            ),
            _StepBack = _SortedRows,
            _KeptFirstRows = Table.FirstN(_StepBack, List.Count(_LG)+1)
        ][_KeptFirstRows],

    GroupedRows = Table.Group(ChangedType, {"FV"}, {{"Fn", Fn_Keep80, type table}}),
    CombinedFn = Table.Combine(GroupedRows[Fn])
in
    CombinedFn
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Einomi ,

 

It seems that you want to implement Pareto/ABC analysis using PowerQuery M. Try the following steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVldc6M6Ev0r1DztVs2HJPQBjxgTmwoGFuzJTd29DyRhHdc4dhbbd2b212+DJAxGdpxcUtc1VTpHLan7dLf055+fvDSNgvzLKPOC0SKLc+ur9fXT50+Ewv/QZ0wpE46NbfjX5rBew89NdVjt4dd7fV2Xu09/fW45Jl7knYend5fBSTQOYgVnpIEDkDAbYXKcXf0Y8JkXx/d6eianZ4gjQvHbs6dhfGtF3lgT2EgSOEgQ4rjkEsNiFGSTMA701jm4wSKXc+YIjIfGHx7KarnaDAnGh/3js0Ua86mioTA9R5i+m8ZG9SIokkdBHYIRIrb7Dp5gs1yvds+Wj497iojr2MS1+Tt48tdiU/PMahpHyLOlpDbpTZbvie+Nk9Yt7fooDKi/t4/F01ZiRt5tGE+sNJl788T6RZECC+UViDObInfIkm73xX5bapYYPu2Qjpq25wWjYgOfGh54cf7lBtarINho6KgsNhoQzLMkmevhtjgDKPfVdrtXoGgRwLZmYettRHCjaetDCRtZrfRqMm8WBfeW9HeFdeWJMkowd7i4GOO+Nxp5k8CaFtWThRH8/VjW07vSTQX4hCuoM1yAXzw8FEsTC0Hw17DY0hAkGOO8OaYrOLLySe4cknDCXJAbhw4j5QSe5qG/mOXWF2uSBa3mUGUEBFvzDb2jfH0tq3MkMuaOJwJ+xgVpvncy6WiRXiGjFzmYNN/VXLPwj2Csl6a2l9DmeyfHcWVKFRF25PdOot7ClO9hzOrvepuynkUgIfLsXbk97+PR+iZPTRnE7eZ7F1F3ZYSq3EGZ/K5mug+iKLnrehJTnsSY3Kb3UnUXqGNMHt31G6Wo+l7JlVde51FZBjL3xRoFuVY7giSDwzCkJM6HWckvqkqrXsvgT714HsRem6bV/lBHcGTbJvHpsARRkIWer03AZoX2y3VZrYrHDui+3cjGwbj0XYaEzYhLz1GUZgaqZgUCM/B3F6a3vZlYBQ22XYGYYzhD08QtA23ipWGQdmMx9PKLDPxK06dhFIWdNFi7HQU1dNyhFvrPq/W6nbBBNtXcUZiZXDURTCDk2oapjRRZK3+YSQEU2OWEQ6p7g+F59Xe501hkdpJmjJowCa1Rcnsx7fvP25U12v6QiCiYBfG8UzJiYw731+VLudm3tZC/Bb/cPJWV3hjzVHqUxCRQLHjxOMg06EwFBbA+bpFNgvm8NZFh5wzuUC3L/V7buGgRWnCgeCWIGLLy4fHw8tCqRA1k6BtH1ngx96dtlEqBr6tF6nLXIBTnaIJ4EoW5JnKk54PPg/ND+FzDw9E3wXrmfGBNgn1z+2vSIUEgKhAyeOOQ5N8HGC4m8sfKUy/urEyVtQJBVctNFdg5vpmR7wNr/AUFYZ9EJYiGAdGr1vgL0/42HQsyBr3LlYbQk3Mnqmapdxphg+CZSU5Wo7bYVsa8wTL2jnGjq/peZI8LHS9jEB69XnNg1iPk0MybJLF1ky3CNo2aEVWx3G4azE0Qx0F0cfRNudmUa2nNTTjJLw9eLeXIiZdFoU6lZ1qXSVGtVyqPQiM4aTXINQ9fbZZ6B6GbTwMr/ZZao8jzW21VmcDlTtMk9PZ0UhWv5Sm6m0dsGXegI/aV6E4KkRmE4MsTT5MsDzJv3Akm2l/r97Lud6bbaldWxRNkVYUM/bqvm6jir806dRw6FEobPuAIH+sGb9mHt1VGTcCQkk7wewd1+7trGMintkEUjIPnd4v0CwRttdCYIHWAynrFkLsvUriXdsFEcBvehb0AaWOqd2K3q58rBUimUeaNQnPylqu83T6vq+JBIaIguM07CbiWFhdBZWEQ8qgsf3RRo+0vqQkyFwlOT+s4OeMpLi73Xf/HDieCsoGhXdgs0W7PkWlRUfmy3XQHg9fnJ5d/yGUcC2YbsRa4/c5EkM9D/7YmUR2Vi6gNfcLA+4Ys8/nCD774SVeCmo6sdl5iD7a3d/TRvT8N9N0LMR579PvxuVTxNvPiSWL9I5yl/7woSrNis9xqSBbmVhqmoGPqbqmfzzFEGmWGJq17rTQLoiRumn0vCr1OkjijLrNyrQ+qhd5BftFaqq7lhIPJRWw47httMwlkiEJNbPNheu2bvdrsO/WBYaNggBy5yKdZkkDvOCp3e4utH3Y1AGG5TY6wEYM6rrNN0h9mh91ztd2+7E5Y0qp8LR5/KBmWO4UJd6mNULtibUSPI/bGnp/M22Spo7u3R3HxVDxu96pHjAN/Dsd8rMyNhXlcPu6Lqq3Lkzisz6VuIspiY/2ykNXEnKP817Zd4rBh65NsVvp4BhTYaqylKuIxckCCh5s2pJAJBAgE/DWXa1hVQEhQKMW6Pd+bHA78NRztDZIrOHavZyAU/uQVn7KCEJtAoLyDw7Xc3iWhZBio0pAg/l9XRiA8Hae3hWePAfLP6ngIVN3n2YxiDv8NlOw8AbGa+wL1nEEdhrHbq4aHBBkI07GFMrpfUoEoKd+ry5Q7L59CpXI3DedBP8iJqn7tjjiYojv1sry+Hb5ZF3srhzpsXcIQRvX0PWvTotqty98tMA7TVrG5fQ6xWb3qufI8PK1jmWmdKaQG2Bir+bcCB16vjRpiyuJR11QwOMshn8Q3QRbEftAtySAvCXBDCEsTRdUlSKEAnXqzvtg3SOnG58DT+xwkvpdTDde3z793xVrVFmlUX7kpK40pLF0flLilySyo37wgFXRr1CFi+1IuwWHajqMGRm0WsM9h1to3Mm82Wsy9uJOUh8vIipeHw76QTUddNvvTMLg5yeOQHR3sDvN455WhhqoY0osyFtD13X8nanJvni9mXm4u49RPXux3hxf1XJOnWf1G1J+LS4lxGQgMtwU9nTV/BdFf9ibu0ajivcYp7adcODYnA801MP1rAWH8VZvimFad//dQtB1DDonq7V4wh0T1n/pUJSTz7o7PR3+oPkPV6I78hgKZ76viZ/cxacDTyIzq121XfvTUq96mqRetHo7JufeOIcsCelErj71WhaiuePvTH5ZFZeWbQulQfheMjxdwxHy7lf8sn8p2fDBX8tpKCToLg5K9J7Hz598v5UX/bEbIscmsniRvbrxhf/R1N0bSrQhjLqFYGDi2L50pBzTfj8+T+jFUCEEgLQ2bl3NUszAOrVqpumzKh5AtXEy5O7yZPsfW5aDq1R+KUSiYDO+zZpL+rZXAyEWG6z0zdlSulysouGafjjWOS4XDxVCmzAyd1zGVGoRNGSSX4VXTJYLZh5egG/aPL6Fl+DhF94XfVnovmENc2xk2Fj0KVbzUwv/xpup8BcS0MbZz5lmv5fnr/w==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Items = _t, Freq = _t, #"Freq_%" = _t, #"Main Seller" = _t, Category = _t, FV = _t]),
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Items", type text}, {"Freq", Int64.Type}, {"Freq_%", Int64.Type}, {"Main Seller", type text}, {"Category", type text}, {"FV", type text}}),
    #"Grouped Rows" = Table.Group(#"Type modifié", {"FV"}, {{"Data", each Table.AddIndexColumn(Table.Sort(_,{"Freq",Order.Descending}), "Index", 1, 1, Int64.Type)}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows",{"Data", (x) => Table.AddColumn(x, "CategoryABC", each if List.Sum(List.FirstN(x[Freq], [Index])) / List.Sum(x[Freq]) <= 0.8 then "A" else "B")}),
    Custom2 = Table.TransformColumns(Custom1, {"Data", each Table.SelectRows(_, each [CategoryABC]="A")}),
    #"Expanded Data" = Table.ExpandTableColumn(Custom2, "Data", {"Items", "Freq", "Freq_%", "Main Seller", "Category", "CategoryABC"}, {"Items", "Freq", "Freq_%", "Main Seller", "Category", "CategoryABC"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Data", each ([CategoryABC] = "A"))
in
    #"Filtered Rows"

ABC classification – DAX Patterns


Best Regards,
Gao
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

  • Einomi's avatar
    Einomi
    Icon for Helper V rankHelper V
    2 years ago

    Anonymous thanks for your suggestion

    However, I need all the rows reaching at least 80% of the category, your solution keeps the rows before reaching the 80%
    Also, when there is only one item in the category it returns B instead of A

    Thanks for your time and for your suggestion