Help with determining division of cost

[amansingh316]

Hi All, 

I need some help with a scenario where I have a list of items with a cost

Item	Cost
Computer	100
Storage	10
Printer	20

Then a list of locations and sub-locations that consume said items, where i must divide the cost equally by location and then by sub-location

Item	Location	Sub-Location
Computer	A	A1
Computer	A	A2
Computer	B	B1
Computer	C	C1
Computer	C	C2
Computer	D	D1

How would i link and the determine the cost if in the above scenario I wanted to charge in the following manner:

Location A-D get charged 25 and then sub-location can be an equal split of the location cost i.e. A1 and A2 get 12.5 each and B1 just gets 25?

Then to be able to sum the total location cost and sub location costs independently in the dashboard, which should always equal the original item cost. Much appreciated.

[dufoq3 · Accepted solution]

Here you are. It could be done easier, but I'd like you to have possibility to see every single step.

let
    TotalCostTable =
        #table(
            type table
            [Item = text, Cost = Currency.Type],
            {{"Computer", 100}, {"Storage", 10}, {"Printer", 20}}
        ),
    DetailTable = 
        #table(
            type table
            [Item = text, #"Sub-Location" = text],
            {{"ComputerA", "A1"}, {"ComputerA", "A2"}, {"ComputerB", "B1"}, {"ComputerC", "C1"}, {"ComputerC", "C2"}, {"ComputerD", "D1"} }
        ),
    AddedLocation = Table.AddColumn(DetailTable, "Location", each Text.Start([#"Sub-Location"], 1), type text),
    ExtractItemMainCategory = Table.TransformColumns(
            AddedLocation,
            {{"Item", each Text.Start(_, Text.Length(_) -1), type text}}
        
    ),
    DetailTable_GroupBy = Table.Group(ExtractItemMainCategory, {"Location"}, {{"Detail", each _, type table [Item=text, #"Sub-Location"=text, Location=text]}, {"Sublocations Qty", each Table.RowCount(_), Int64.Type}}),
    ExpandedDetail = Table.ExpandTableColumn(DetailTable_GroupBy, "Detail", {"Item", "Sub-Location"}, {"Item", "Sub-Location"}),
    // Merge with query itself. Left Side = 'ExpandedDetail' step, Right Side = 'TotalCostTable' step.
    MergedQueries = Table.NestedJoin(ExpandedDetail, {"Item"}, TotalCostTable, {"Item"}, "ExtractItemMainCategory", JoinKind.LeftOuter),
    ExpandedQuery = Table.ExpandTableColumn(MergedQueries, "ExtractItemMainCategory", {"Cost"}, {"Total Item Cost"}),
    AddedLocationsQty = Table.AddColumn(ExpandedQuery, "Locations Qty", each List.Count(List.Distinct(ExpandedQuery[Location])), Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(AddedLocationsQty,{"Item", "Location", "Sub-Location", "Locations Qty", "Sublocations Qty", "Total Item Cost"}),
    AddedLocationCost = Table.AddColumn(#"Reordered Columns", "Location Cost", each [Total Item Cost] / [Locations Qty], Currency.Type),
    AddedSubLocationCost = Table.AddColumn(AddedLocationCost, "Sub-Location Cost", each [Location Cost] / [Sublocations Qty], Currency.Type),
    FinalCostTable = Table.SelectColumns(AddedSubLocationCost,{"Item", "Location", "Sub-Location", "Total Item Cost", "Location Cost", "Sub-Location Cost"}),
    LocationDetail_GroupBy = Table.Group(FinalCostTable, {"Location", "Item"}, {{"Location Cost", each List.Average([Location Cost]), Currency.Type}}),
    SubLocationDetail_GroupBy = Table.Group(FinalCostTable, {"Sub-Location", "Item"}, {{"Sub-Location Cost", each List.Average([#"Sub-Location Cost"]), Currency.Type}})
in
    SubLocationDetail_GroupBy