Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.