Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Anonymous
Not applicable

How to create a sales summary table that can be filtered by item, date, and vendor.

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

ItemCostVendor
A$10Alpha
B$2Bravo
C$5Delta

 

Sales

ItemQuantityDate
A109/1/2021
A110/1/2021
C109/5/2021
KIT_1110/3/2021
KIT_1110/1/20/21
KIT_2309/7/2021

 

KIT_Breakdown

Kit_#ItemQuantity_per Cost
KIT_1C1$5
KIT_1A2$10
KIT_2B5$2
KIT_2A3$10

 

Result Table

ItemQuantityTotal CostDateVendor
A1$109/1/2021Bravo
A3$3010/1/2021Bravo
A9$9009/7/2021Bravo
A2$2010/3/2021Bravo
B15$3009/7/2021Acme
C1$509/5/2021Delta
C1$510/3/2021Delta
C1$510/1/2021Delta
Total33$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.

 

 

1 ACCEPTED SOLUTION
DataInsights
Super User
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

 

DataInsights_1-1634767800550.png

 

2. Data model:

 

DataInsights_0-1634767742577.png

 

3. Measures:

 

Sum Quantity = SUM ( Sales[Item Quantity] )

 

Total Cost = SUMX ( 'Item', 'Item'[Cost] * [Sum Quantity])

 

4. Result:

 

DataInsights_2-1634767890973.png

Item is from the Item table.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

1 REPLY 1
DataInsights
Super User
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

 

DataInsights_1-1634767800550.png

 

2. Data model:

 

DataInsights_0-1634767742577.png

 

3. Measures:

 

Sum Quantity = SUM ( Sales[Item Quantity] )

 

Total Cost = SUMX ( 'Item', 'Item'[Cost] * [Sum Quantity])

 

4. Result:

 

DataInsights_2-1634767890973.png

Item is from the Item table.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Solution Authors
Top Kudoed Authors