Sum duplicate value of materials by region, country

[tietvinhphuc]

Hi everyone,

I have table of material number with plant, country, region, quantity and value.

The materials can exists in serveral plants.

It can happen several plants in a country.

And I want to explore the duplicated materials

I want to create a table to show by region, country and how many materials are duplicated and sum of duplicated quantity and value. And as data above, the duplicated found in Germany, Europe and total value is 60.83 +0 = 60.83, total duplicated part 7+4 = 11

Thanks,

BRs,

Phuc

[FreemanZ]

hi @tietvinhphuc ,

try like:

duplicate qty=

SUMX(

FILTER(

ADDCOLUMNS(

SUMMARIZE(data, data[region], data[country]),

"Count", CALCULATE(COUNTROWS(data)),

"Qty", CALCULATE(SUM(data[quantity])

),

[Count]>1

),

[Qty]

)

duplicate value=

SUMX(

FILTER(

ADDCOLUMNS(

SUMMARIZE(data, data[region], data[country]),

"Count", CALCULATE(COUNTROWS(data)),

"@value", CALCULATE(SUM(data[value])

),

[Count]>1

),

[@value]

)

[shafiz_p · Accepted solution]

Hi @tietvinhphuc 

Create below 3 measure:

Measure 1:

DuplicatedMaterials = 
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        'Table',
        COUNTROWS(
            FILTER(
                'Table',
                'Table'[MATNR] = EARLIER('Table'[MATNR])
            )
        ) > 1
    )
)

Measure 2:

SumDuplicatedQuantity = 
CALCULATE(
    SUM('Table'[Quantity]),
    FILTER(
        'Table',
        COUNTROWS(
            FILTER(
                'Table',
                'Table'[MATNR] = EARLIER('Table'[MATNR])
            )
        ) > 1
    )
)

Measure 3:

SumDuplicatedValue = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        'Table',
        COUNTROWS(
            FILTER(
                'Table',
                'Table'[MATNR] = EARLIER('Table'[MATNR])
            )
        ) > 1
    )
)

Output:

All the other regions are blank , that's why not showing those rows. If you need to show rows with no data, follow below steps:
Right click on Region column and select show item with no data. See image:

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz