Forum Discussion

claudedubois's avatar
claudedubois
Regular Visitor
3 years ago

Sorting by total spend, getting cumulative % , and categorizing

Hello everyone,    I hope all is well.   Kindly need your help with the function below. Able to do it in Excel but not on PBI.    This is the easy option to do it, the more complex one is to th...
Need Help
AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
3 years ago

claudedubois 

I still do not see the data in a format that I can copy. For a solution in Power Query, Place the following M code in a blank query to see the steps.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s0vLU5V0lEyVYrViVbyTq1Myk8sSgEKGBqARXwSC0ryC4B8EwOIQHByUWpqHlDACCrgnJFYlJ5aBBYBCwwBI2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SKU = _t, Spend = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SKU", type text}, {"Spend", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"SKU"}, {{"Total", each List.Sum([Spend]), type nullable number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Total", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    sumTotal_ = List.Sum(#"Added Index"[Total]),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Sum(List.FirstN(#"Added Index"[Total],[Index])) / sumTotal_, Percentage.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Total", "Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Total"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Custom", each if [Total] > 0.95 then "Tail" else "Non-Tail", type text),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Total"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "Total"}})
in
    #"Renamed Columns1"

 

 

 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.