How to find percentage with duplicate targets

[Deepak3Arora]

Hi Team,

What would be best way to find the percentage with duplcate Target values.

There are duplicate values in Target column hence causing issue to me

My Data is as below:

SupplierPeriodValueTarget

S1	P1	20	200
S1	P2	30	200
S2	P1	40	250
S2	P2	50	250
S3	P1	10	100

I Need to find percentage of work completed for selected suppliers in filter in terms of Value against the Target.

Regards

Deepak

[v-jingzhang]

Hi @Deepak3Arora 

Here are some DAX methods for your reference. If you want to add a calculated column in the original table, you can try below column code. 

Column = 
DIVIDE (
    CALCULATE ( SUM ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Supplier] ) ),
    CALCULATE ( MAX ( 'Table'[Target] ), ALLEXCEPT ( 'Table', 'Table'[Supplier] ) )
)

If you want to use a measure, you can try below measure and add it to a visual along with Supplier column. 

Measure 2 = DIVIDE(SUM('Table'[Value]),MAX('Table'[Target]))

If you want a new table with DAX, you can try 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[ChielFaber · Accepted solution]

If the objective is to sum the values of the two periodes and keep the target consistent. You can do a group by

on Supplier with the UI group by function. Choose advanced.

Sum the value column

Max the Target column.

You then get de value per supplier and the target without the duplicate rows. You could then just add a custom column with the needed calculation to get to the desired percentage.