Excel urgent help

Hi All,

I have a problem in creating formula and i need your help if you can, The below sheet is for achievement VS. target by sku, what i need to do next for the people who achieved over 100% i need to calculate their commission based on the sku value so im tracking it in daily basis but what i need is a formula to tell me which sku is overachieved

Do you think this is possible?

Name	Target	Sku1	Sku2	Sku3	Sku4	Sku5	Sku6	Sku7	Total Achv	%
Abdallah	15	11	5	1	4	2	3	1	27	180%
Ali	15	5	2	2	3	0	1	0	13	87%
Ahmed	15	9	2	3	4	0	1	0	19	127%
khaled	15	9	2	3	4	0	1	1	20	133%
ziad	15	9	2	3	4	0	1		19	127%

MarcelBeug

8 years ago

Just to illustrate that this topic was much more complicated than suggested in the questions raised, these are the queries I created.

Query Oct Tracker to import the Excel sheet:

let
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Tracker - Copy.xlsx"), null, true),
    #"Oct Tracker_Sheet" = Source{[Item="Oct Tracker",Kind="Sheet"]}[Data],
    #"Removed Blank Rows" = Table.SelectRows(#"Oct Tracker_Sheet", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
    #"Removed Blank Rows"

Query Targets with the targets for each name, replacing 0 by infinity:

let
    Source = #"Oct Tracker",
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Column1", "Column3", "Column4"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Other Columns", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Location/ Nsame", type text}, {"Target X", Int64.Type}, {"Target Z", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Target X] <> null and [Target Z] <> null)),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.Contains([#"Location/ Nsame"], "Total")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"Location/ Nsame", "Name"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",0,Number.PositiveInfinity,Replacer.ReplaceValue,{"Target X", "Target Z"})
in
    #"Replaced Value"

Query Categories:

#table(type table[Sku = text, Category = text],
{{"Sku100", "X"},
 {"Sku150", "X"},
 {"Sku200", "Z"},
 {"Sku60", "X"},
 {"Sku250", "Z"},
 {"Sku350", "Z"},
 {"Sku750", "Z"},
 {"Sku1000", "Z"},
 {"Sku300", "Z"}})

Query Result:

let
    Source = #"Oct Tracker",
    #"Filtered Rows1" = Table.SelectRows(Source, each ([Column1] = null and [Column5] <> null) or not Text.StartsWith([Column1], "Location ")),
    #"Transposed Table" = Table.Transpose(#"Filtered Rows1"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filled Down", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Location/ Nsame", "Day"}, {"Column2", "Sku"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [Day] is number and [Sku] <> "Total"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Day", "Sku"}, "Name", "Value"),
    #"Merged Queries" = Table.NestedJoin(#"Unpivoted Other Columns",{"Sku"},Categories,{"Sku"},"Categories",JoinKind.LeftOuter),
    #"Expanded Categories" = Table.ExpandTableColumn(#"Merged Queries", "Categories", {"Category"}, {"Category"}),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Expanded Categories",{{"Name", Order.Ascending}, {"Category", Order.Ascending}, {"Day", Order.Ascending}})),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Day", "Name", "Category"}, {{"Sku", each Text.Combine([Sku],", "), type text}, {"Value", each List.Sum([Value]), Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries1" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Previous",JoinKind.LeftOuter),
    #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries1", "Previous", {"Name", "Category"}, {"Previous.Name", "Previous.Category"}),
    #"Sorted Rows1" = Table.Sort(#"Expanded Previous",{{"Index", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows1", "StartIndex", each if [Name] <> [Previous.Name] or [Category] <> [Previous.Category] then [Index] else null),
    #"Filled Down1" = Table.FillDown(#"Added Custom",{"StartIndex"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down1", "Cumulative", each List.Sum(List.Range(#"Filled Down1"[Value],[StartIndex],[Index.1] - [StartIndex])), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "Index.1", "Previous.Name", "Previous.Category", "StartIndex"}),
    #"Merged Queries2" = Table.NestedJoin(#"Removed Columns",{"Name"},Targets,{"Name"},"Targets",JoinKind.LeftOuter),
    #"Expanded Targets" = Table.ExpandTableColumn(#"Merged Queries2", "Targets", {"Target X", "Target Z"}, {"Target X", "Target Z"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Targets", "Cumulative Over Target", each List.Max({0,[Cumulative] - (if [Category] = "X" then [Target X] else [Target Z])}), Int64.Type),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Single Value Over Target", each List.Min({[Value],[Cumulative Over Target]}), Int64.Type),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom3",Number.PositiveInfinity,0,Replacer.ReplaceValue,{"Target X", "Target Z"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Day", Int64.Type}})
in
    #"Changed Type"

10 Replies

MarcelBeug

Community Champion

8 years ago

Do you mean that you want the first Sku for which the target was exceeded?

So:
Sku2
null
Sku4
Sku4
Sku4

MarcelBeug

Community Champion

8 years ago

I thought it was urgent, so why no feedback yet?

Anyhow, assuming I guessed right, it can be done with this query:

let
    // Input from another query Table1, in which the Excel table is imported into Power Query:
    Source = Table1,

    // Names of columns starting with "Sku", so this will be dynamic:
    RequiredColumns = List.Select(Table.ColumnNames(Source), each Text.StartsWith(_,"Sku")),

    // Adding a column with nested lists, containing the values from the "sku*" columns:
    #"Added Values" = Table.AddColumn(Source, "Values", each Record.FieldValues(Record.SelectFields(_,RequiredColumns))),

    // Transforming the nested lists with values in accumulated values:
    Accumulated = Table.TransformColumns(#"Added Values",{{"Values", each List.Accumulate(List.Skip(_), {_{0}}, (Cumulation,Value) => Cumulation & {List.Sum({List.Last(Cumulation),Value})})}}),

    // Transforming the nested lists into a list of lists, each with the name of the Sku and the target minus the accumulated values.
    // So this looks like for the first row: {{"Sku1", 4}, {"Sku2", -1}, etcetera}
    #"Added SkuAndValues" = Table.AddColumn(Accumulated, "Overachieved Sku", (ThisRow) => List.Zip({RequiredColumns,List.Transform(ThisRow[Values], each ThisRow[Target] - _)})),

    // Selecting the first Sku with negative value (i.e. Target exceeded); default value is null:
    #"Selected First Sku" = Table.TransformColumns(#"Added SkuAndValues",{{"Overachieved Sku", each List.First(List.Select(_, each _{1} < 0),{null}){0}, type text}}),

    // Removing temporary column:
    #"Removed Columns" = Table.RemoveColumns(#"Selected First Sku",{"Values"})
in
    #"Removed Columns"

abdallah1
Regular Visitor
8 years ago
Hi Dear,

Sorry for the late reply but im not feeling very well.
what i mean is company for example has 7 sku devided to 2 categories smart and none smart, person has target of the 2 categories and when the person achieve 100% of his target i need to calculate his commission based in different price per sku therefore what i need the formula for is to give me how many of each sku over ahcieved after the 100%.
i hope i was able to explain it to you and thanks a lot in advance.

Forum Discussion

Excel urgent help

10 Replies

Related content

Urgent question about Policy update

Need Urgent Help on SamePeriodLastYear

urgent help needed

Urgent Help - Power Query - Data Transformation

Urgent ASAP: Paginated Report SubscriptionFailure-AdalExternalSecurityChallengeNotSatisfiedErrorCode