cancel
Showing results for
Did you mean:
Regular Visitor

## 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

 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!

1 ACCEPTED SOLUTION
Super User

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"}
),
ExpandKitBreakdown,
"Item Quantity",
each
if [Kit_Breakdown.Quantity_per] = null then
[Quantity]
else
[Quantity] * [Kit_Breakdown.Quantity_per]
),
ChangeType2 = Table.TransformColumnTypes(
{{"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.

Super User

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"}
),
ExpandKitBreakdown,
"Item Quantity",
each
if [Kit_Breakdown.Quantity_per] = null then
[Quantity]
else
[Quantity] * [Kit_Breakdown.Quantity_per]
),
ChangeType2 = Table.TransformColumnTypes(
{{"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.

Announcements

#### 2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.