Sorting by total spend, getting cumulative % , and categorizing

[claudedubois]

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 the same logic but only compare SKUs bought in a specific date (a column with date is also available). In this case, an SKU can be tail in one year but non-tail in another year. 

Any help would be much much appreciated, thank you. 

[tamerj1]

Hi @claudedubois 
Please refer to attached sample file with DAX (Measure) solution

Result = 
VAR CurrentSKU = 
    SELECTEDVALUE ( 'Table'[SKU] )
VAR T1 =
    ADDCOLUMNS ( 
        ALLSELECTED ( 'Table'[SKU] ),
        "@Spend", CALCULATE ( SUM ( 'Table'[Spend] ) )
    )
VAR TotalSpend = 
    SUMX ( T1, [@Spend] )
VAR T2 = 
    GENERATE ( 
        T1,
        VAR CumulativeSpend = 
            SUMX ( 
                FILTER ( T1, [@Spend] >= EARLIER ( [@Spend] ) ),
                [@Spend]
            )
        VAR CumulativePercentage =
            DIVIDE ( CumulativeSpend, TotalSpend )
        RETURN
            ROW ( "@Percentage", CumulativePercentage )
    )
VAR T3 = 
    FILTER ( T2, [SKU] = CurrentSKU )
VAR CurrentPercentage = 
    MAXX ( T3, [@Percentage] )
RETURN
    IF ( CurrentPercentage > 0.95, "Tail", "Non - Tail" )

[AlB]

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

[AlB]

Hi @claudedubois 

1. Are you looking to do this in power query or in DAX?

2. Can you share the sample data on the left above in text-tabular format instead of in a screen cap, so that it can be copied?

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.

[claudedubois]

Hey, thanks a lot for checking my post, the data is below in tabular form. 

Regarding DAX vs. power query, I'm not sure as I don't know if you can actually do it in both. I think in DAX it's almost impossible correct? Either method that works is fine.