Excel urgent help

[abdallah1]

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]

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

So:
Sku2
null
Sku4
Sku4
Sku4

[MarcelBeug]

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]

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.

[MarcelBeug]

Hi. sorry you are not feeling well, this time of the year.

I would be glad to help you, but I really don't understand your requirements.

Also an additional requirement seems to have popped up: you didn't mention categories in your initial post.

Moreover: in your initial question you mentioned that you need the Sku which is overachieved; in your latest post you want "how many of each sku over achieved after the 100%", which is clearly a different question.

Can you elaborate an example, e.g. what output would you expect for Abdallah and why?

Initially I thought it would be Sku2, as Sku1 has 11 units, next would be Sku2 with 5 units, so if Sku2 comes after Sku1, Sku2 would be the Sku that exceeds the target of 15, as 11 + 5 = 16.

[abdallah1]

https://drive.google.com/file/d/1h0kEjWyBVp16CVsK4RxJggxM1X2tCja7/view?usp=sharing

Hi, Thanks for the spirit and merry christmas to you.

I have attached the sheet im working on, i hope when you take a look into it you will be able to understand what exactly i want to do.

regards

[MarcelBeug]

Well, I think that it is almost clear what you want to do, which is a lot more than what you explained so far.

In fact you have a table for each day of the month.

And Sku's are divided in categrories:

Sku Category

Sku100	X
Sku150	X
Sku200	Z
Sku60	X
Sku250	Z
Sku350	Z
Sku750	Z
Sku1000	Z
Sku300	Z

The picture below illustrates how the result could look like.

In this example, Name 12 has a target of 25 for category X. On the 18th, he has a cumulative value of 24.
On the 19th, he has a value of 2 for Sku100 and also 2 for Sku150, each in category X.

So 3 are overachieved, but you can't know if these are 1x Sku100 and 2x Sku150, or 2x Sku100 and 1x Sku150.

What would you expect in such case?

[abdallah1]

This is actually the problem,

I'm not able to create a formula or so to identify which sku the person has overachieved because they will get paid by value not volume.

now you feel my pain, sorry if wasn't able to explain this clearly from the beginning, do you think there is a way to do so?

[MarcelBeug]

My suggestion would be to define a rule, e.g. in the previous example where you had 3 out of 4 overachieved, take 75% of the total value that would have been applicable in case all 4 would hae been overachieved.

[MarcelBeug · Accepted solution]

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"

[abdallah1]

Thank you so much Marcel for your help, I just don’t know how to execute this on excel but I really appreciate your support.