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

 

 

Need Help

4 Replies

  • AlB's avatar
    AlB
    Community Champion
    3 years ago

    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's avatar
      claudedubois
      Regular Visitor
      3 years ago

      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. 

  • AlB's avatar
    AlB
    Community 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.

     

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    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" )