Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Galileo1627
New Member

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

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

 

Galileo1627_1-1711551423310.png

Sum Invoice = SUMX(DISTINCT('EXP VW_EXP_FIM25_Debtors'[ninv_InvoiceNumber]),CALCULATE(AVERAGE('EXP VW_EXP_FIM25_Debtors'[balh_Balance_HC])))
3 REPLIES 3
Anonymous
Not applicable

Hi @Galileo1627 ,

I create a table as you mentioned.

vyilongmsft_0-1711591029356.png

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.

vyilongmsft_1-1711591264112.png

 

 

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.

 

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

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.