Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
Hi,
I have a sales table that lists how many items were sold from my Item List table. Some items in my Item List table (prefixed with KIT) are composed of individual items from the Item List table. This is broken down in the Kit_Breakdown table. I want to get the total value of each item sold from the Sales table by date in a table visualization (ultimately, I plan on using a bar chart).
Below are how my tables are composed, and the result table is how I want the a table visualization to look like.
I know how to use LOOKUPVALUE to get the cost for each item into the Kit_Breakdown table. Same for the cost. But I can't figure out how to get my table visualization to output the correct totals (bolded below in Result table)
Item
Item | Cost | Vendor |
A | $10 | Alpha |
B | $2 | Bravo |
C | $5 | Delta |
Sales
Item | Quantity | Date |
A | 1 | 09/1/2021 |
A | 1 | 10/1/2021 |
C | 1 | 09/5/2021 |
KIT_1 | 1 | 10/3/2021 |
KIT_1 | 1 | 10/1/20/21 |
KIT_2 | 3 | 09/7/2021 |
KIT_Breakdown
Kit_# | Item | Quantity_per | Cost |
KIT_1 | C | 1 | $5 |
KIT_1 | A | 2 | $10 |
KIT_2 | B | 5 | $2 |
KIT_2 | A | 3 | $10 |
Result Table
Item | Quantity | Total Cost | Date | Vendor |
A | 1 | $10 | 9/1/2021 | Bravo |
A | 3 | $30 | 10/1/2021 | Bravo |
A | 9 | $90 | 09/7/2021 | Bravo |
A | 2 | $20 | 10/3/2021 | Bravo |
B | 15 | $30 | 09/7/2021 | Acme |
C | 1 | $5 | 09/5/2021 | Delta |
C | 1 | $5 | 10/3/2021 | Delta |
C | 1 | $5 | 10/1/2021 | Delta |
Total | 33 | $195 |
My table relationships are as follows -
Item Kit Breakdown
Item 1:* Item
Sales Kit Breakdown
Item *:* Kit_# (Single filter Sales to Kit Breakdown)
Calendar Sales
Date 1:* Date
Any help is greatly appreciated!
Let me know if more information is needed.
Solved! Go to Solution.
@Anonymous,
One approach is to solve this via data modeling. Since Sales[Item] contains both items and kits, you can decompose kits into their component items, resulting in Sales[Item] containing only items. This will simplify the data model and DAX.
1. Power Query steps in Sales table:
let
Source = <My Data Source>,
ChangeType = Table.TransformColumnTypes(Source, {{"Quantity", Int64.Type}, {"Date", type date}}),
MergeKitBreakdown = Table.NestedJoin(
ChangeType,
{"Item"},
Kit_Breakdown,
{"Kit_#"},
"Kit_Breakdown",
JoinKind.LeftOuter
),
ExpandKitBreakdown = Table.ExpandTableColumn(
MergeKitBreakdown,
"Kit_Breakdown",
{"Kit_#", "Item", "Quantity_per"},
{"Kit_#", "Kit_Breakdown.Item", "Kit_Breakdown.Quantity_per"}
),
AddItemQuantity = Table.AddColumn(
ExpandKitBreakdown,
"Item Quantity",
each
if [Kit_Breakdown.Quantity_per] = null then
[Quantity]
else
[Quantity] * [Kit_Breakdown.Quantity_per]
),
AddItemNew = Table.AddColumn(AddItemQuantity, "Item New", each [Kit_Breakdown.Item] ?? [Item]),
ChangeType2 = Table.TransformColumnTypes(
AddItemNew,
{{"Item Quantity", Int64.Type}, {"Item New", type text}}
),
RemoveColumns = Table.RemoveColumns(
ChangeType2,
{"Item", "Kit_Breakdown.Item", "Kit_Breakdown.Quantity_per", "Quantity"}
),
RenameColumns = Table.RenameColumns(RemoveColumns, {{"Item New", "Item"}})
in
RenameColumns
2. Data model:
3. Measures:
Sum Quantity = SUM ( Sales[Item Quantity] )
Total Cost = SUMX ( 'Item', 'Item'[Cost] * [Sum Quantity])
4. Result:
Item is from the Item table.
Proud to be a Super User!
@Anonymous,
One approach is to solve this via data modeling. Since Sales[Item] contains both items and kits, you can decompose kits into their component items, resulting in Sales[Item] containing only items. This will simplify the data model and DAX.
1. Power Query steps in Sales table:
let
Source = <My Data Source>,
ChangeType = Table.TransformColumnTypes(Source, {{"Quantity", Int64.Type}, {"Date", type date}}),
MergeKitBreakdown = Table.NestedJoin(
ChangeType,
{"Item"},
Kit_Breakdown,
{"Kit_#"},
"Kit_Breakdown",
JoinKind.LeftOuter
),
ExpandKitBreakdown = Table.ExpandTableColumn(
MergeKitBreakdown,
"Kit_Breakdown",
{"Kit_#", "Item", "Quantity_per"},
{"Kit_#", "Kit_Breakdown.Item", "Kit_Breakdown.Quantity_per"}
),
AddItemQuantity = Table.AddColumn(
ExpandKitBreakdown,
"Item Quantity",
each
if [Kit_Breakdown.Quantity_per] = null then
[Quantity]
else
[Quantity] * [Kit_Breakdown.Quantity_per]
),
AddItemNew = Table.AddColumn(AddItemQuantity, "Item New", each [Kit_Breakdown.Item] ?? [Item]),
ChangeType2 = Table.TransformColumnTypes(
AddItemNew,
{{"Item Quantity", Int64.Type}, {"Item New", type text}}
),
RemoveColumns = Table.RemoveColumns(
ChangeType2,
{"Item", "Kit_Breakdown.Item", "Kit_Breakdown.Quantity_per", "Quantity"}
),
RenameColumns = Table.RenameColumns(RemoveColumns, {{"Item New", "Item"}})
in
RenameColumns
2. Data model:
3. Measures:
Sum Quantity = SUM ( Sales[Item Quantity] )
Total Cost = SUMX ( 'Item', 'Item'[Cost] * [Sum Quantity])
4. Result:
Item is from the Item table.
Proud to be a Super User!
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.