Forum Discussion

Cednok's avatar
Cednok
Frequent Visitor
5 years ago
Solved

Exclude duplicate reference from a calculation

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 o...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    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.

amitchandak's avatar
amitchandak
Super User
5 years ago

Cednok ,

Try like (measure)

 

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

 

Distinctcount(Table[Ref])

Cednok's avatar
Cednok
Frequent Visitor
5 years ago

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 ?