How to pull one invoice amount when duplicate invoice #s are in data set

[Galileo1627]

Screenshot below. Tried to write this formula below to have invoice amount only show once but didn't work. Any advice?

Sum Invoice = SUMX(DISTINCT('EXP VW_EXP_FIM25_Debtors'[ninv_InvoiceNumber]),CALCULATE(AVERAGE('EXP VW_EXP_FIM25_Debtors'[balh_Balance_HC])))

[Anonymous]

Hi @Galileo1627 ,

I create a table as you mentioned.

If you want to output any of the values in the duplicate values, I think you can use the following string of DAX code.

Sum Invoice =
VAR _A =
    SUMX (
        DISTINCT ( 'EXP VW_EXP_FIM25_Debtors'[ninv_InvoiceNumber] ),
        CALCULATE ( AVERAGE ( 'EXP VW_EXP_FIM25_Debtors'[balh_Balance_HC] ) )
    )
RETURN
    IF ( MAX ( 'EXP VW_EXP_FIM25_Debtors'[Days Overdue] ) = 2, _A )

Then you will get what you want.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Galileo1627]

Hi Zhou,

I appreciate your reply, but those invoices are just one example. All invoices are duplicated and the days overdue are all different, not just two and one days. Is there a forumla we can write to remove all duplicates?

[Anonymous]

Hi @Galileo1627 ,

I think if you can provide me with more data about your table I can try to dig deeper. Also, to remove duplicate values I think you can use DISTINCTCOUNT or COUNTROWS(DISTINCT(...)) , maybe it can help you. I hope this topic can help you: Solved: How to remove duplicate from measure - Microsoft Fabric Community

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.