Forum Discussion

Regular Visitor

8 years ago

Solved

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

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"

MarcelBeug

Community Champion

8 years ago

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

Regular Visitor

8 years ago

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
Community Champion
8 years ago
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

Community Champion

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"

abdallah1
Regular Visitor
8 years ago
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.

Forum Discussion

Excel urgent help

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