Data Duplication

[JBF1978]

I have a spreadsheet listing parts needed to build tools. One part can be used in multiple tools (Tool Type), and the quantity needed depends on the tool's configuration and tool type. There isn't a one-to-one relationship, as different tools and configurations require different amounts of the same part. When I try to forecast based on orders in our system, the quantities sometimes double or triple incorrectly. Example table attached, I have this for multiple parts, but this is a good example overall. 

Having trouble getting this to sum up correctly, any help would be appreciated. 

Part #	Part Name	Tool	Tool Configuration	Qty Needed
100010	Plug	24DM	129306	22
100010	Plug	24DML	113660	24
100010	Plug	24DMLS	113651	44
100010	Plug	24DMLSX	111481	44
100010	Plug	24DMLX	110470	44
100010	Plug	24DMS	113651	44
100010	Plug	24DMSX	111481	44
100010	Plug	24DMX	129307	22
100010	Plug	24DUL	130193	22
100010	Plug	24DULS	130191	22
100010	Plug	24DULSX	130190	22
100010	Plug	24DULX	130192	22
100010	Plug	26DM	129308	24
100010	Plug	26DML	122703	48
100010	Plug	26DML	122713	48
100010	Plug	26DMLS	122701	48
100010	Plug	26DMLS	122711	48
100010	Plug	26DMLSX	122700	48
100010	Plug	26DMLSX	122710	48
100010	Plug	26DMLX	122702	48
100010	Plug	26DMLX	122712	48
100010	Plug	26DMX	129309	24
100010	Plug	30DM	115603	56
100010	Plug	30DM	129310	28
100010	Plug	30DML	115603	56
100010	Plug	30DMLS	115605	56
100010	Plug	30DMLSX	115606	56
100010	Plug	30DMLX	115604	56
100010	Plug	30DMX	129311	28
100010	Plug	30DUL	130199	28
100010	Plug	30DULS	130197	28
100010	Plug	30DULSX	130196	28

[rajendraongole1 · Accepted solution]

Hi @JBF1978  - Try using SUMX to ensure proper summation based on distinct Tool Configuration

Total Qty Needed =
SUMX(
VALUES(YourTable[Tool Configuration]),
MAX(YourTable[Qty Needed])
)

also in your power query editor, please do group by :

Go to Power Query (Transform Data) and:

Group By Part #, Tool, Tool Configuration, and sum Qty Needed.
Remove duplicate entries before loading into Power BI.

I hope this helps.

[Anonymous · Accepted solution]

Hi,rajendraongole1 ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.

Hi,@JBF1978 .I am glad to help you.
Like this?
Instead of 22, 44, the data is aggregated to 44, 88, etc.

If you can't find a suitable visual to display the data, I hope my suggestions below will be helpful to you.
Since you can only add one field as a grouping condition in a line chart, you mentioned that “the number of fields required depends on the configuration of the tool and the type of tool”.
So you could try to place one field on the X axis and one field on the Legend field, to avoid data aggregation (double or triple the data as you mentioned).

Perhaps you might consider adding a slicer

If you want to consider other options, grouping the Qty calculation based on the two categorization fields is a good way to go:

What this metric does is calculate the sum of the Qty Needed for all rows that have the same Part #, Part Name, Tool Configuration, and Tool as the current row (i.e., grouping based on these four fields, I'm assuming you have multiple lesser types of [Part Name],[Part#])

M_groupNeed = 
VAR _part =
    MAX ( 'TestTable'[Part #] )
VAR _partName =
    MAX ( 'TestTable'[Part Name] )
VAR _toolConfig =
    MAX ( 'TestTable'[Tool Configuration] )
VAR _tool =
    MAX ( 'TestTable'[Tool] )
// _part: get the Part # value of the current row.
// _partName: get the Part Name value of the current row.
// _toolConfig: get the Tool Configuration value of the current row.
// _tool: get the Tool value for the current row.
VAR _groupNeed =
    CALCULATE (
        SUM ( 'TestTable'[Qty Needed] ),
        FILTER (
            ALL ( TestTable ),
            'TestTable'[Part #] = _part
                && 'TestTable'[Part Name] = _partName
                && 'TestTable'[Tool Configuration] = _toolConfig
                && 'TestTable'[Tool] = _tool
        )
    )
RETURN
    _groupNeed

You can also use the ALLEXCEPT function, he can be prior to the same effect, according to the specified field to group the data, and then perform each group within the aggregation operations

M_ExceptGroupNeed = 
CALCULATE (
    SUM ( 'TestTable'[Qty Needed] ),
    ALLEXCEPT (
        TestTable,
        'TestTable'[Part #],
        'TestTable'[Part Name],
        'TestTable'[Tool Configuration],
        'TestTable'[Tool]
    )
)

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajendraongole1 · Accepted solution]

Hi @JBF1978  - Try using SUMX to ensure proper summation based on distinct Tool Configuration

Total Qty Needed =
SUMX(
VALUES(YourTable[Tool Configuration]),
MAX(YourTable[Qty Needed])
)

also in your power query editor, please do group by :

Go to Power Query (Transform Data) and:

Group By Part #, Tool, Tool Configuration, and sum Qty Needed.
Remove duplicate entries before loading into Power BI.

I hope this helps.