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.

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.

Cednok's avatar
Cednok
Frequent Visitor
5 years ago

Thank you very much