Exclude duplicate reference from a calculation

[Cednok]

Hello the Community,

I have to calculate a percentage of anomaly on the sales of some product.
On the file of anomaly my problem is that I have several type of anomaly on the same reference so on a reference the anomaly is counted several times.

How to make sure that if a reference is present several times, only one is counted in the calculation?

Thanks in advance

[v-yalanwu-msft · Accepted solution]

Hi @Cednok ,  

In my understanding, You could create a measure by the following formula:

anomaly  percentage = DISTINCTCOUNT([REF])/CALCULATE(COUNT([REF]),FILTER(ALL('Table'),[Article Type]="Sold to"))

The final output is shown below:  

If it is incorrect, you could try to another measure by the following formula:

anomaly  percentage2 = DISTINCTCOUNT([REF])/COUNT([REF])

The final output is shown below:  

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Cednok]

Thank you very much

[amitchandak]

@Cednok ,

Try like (measure)

countrows(summarize(Table, Table[ref]))

Distinctcount(Table[Ref])

[Cednok]

Thank you but these measurs will tell me how many duplicate references I have, it will not allow me to do the calculation

I may have badly expressed my need

I have to calculate a percentage of anomaly on the reference of articles but I have several anomalies on the same references.

But if several anomalies on the same reference = 1 anomaly
For example on the file I should have 8 real anomaly

For examples At the moment I have 10 articles "sold to", on the file anomaly you can see 5 anomalies but on 3 references the calculation should be 3/10 and not 5/10

How to make so that the calculation is taken into account only on the distinct references ?