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"

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