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 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

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.

4 Replies

  • 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 ?

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    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.