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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GeorgeCs
Frequent Visitor

DAX Measure to summarise only unique invoice # totals in curated dataset.

Hi team,
I am trying to solve a SUMX and DISICNT challenge, potentially.
The goal is to create a DAX measure where I can sum all the unique invoice totals, BUT I am facing with a challenge of duplicates in invoice #.
 

GeorgeCs_0-1685330418133.png

Initially, I have created a measure: 

  1. Max Invoice Total Excl GST = MAX([Invoice Total Excl GST])

&

  1. Distinct Sum Invoice Total Excl GST = SUMX(DISTINCT([Invoice Number]), [Max Invoice Total Excl GST])

It did work, but then I found out the duplicates of invoice #.

 

Is there a way to using the ID and also invoice # as part of the measure? 

I am using a curated dataset.

Thank you
G.

 

 

1 ACCEPTED SOLUTION

@GeorgeCs 
Summarize GST =
SUMX ( VALUES ( 'Table CV'[GST] ), 'Table CV'[GST] )

Mahesh0016_0-1685422115310.png

@GeorgeCs I hope this helps you!!THANK YOU!!

View solution in original post

9 REPLIES 9
kristel_tulio
Helper III
Helper III

Hi @GeorgeCs ,

 

Try it like this if you have one table for all the columns in your visual. If you want the unique in Invoice just change the VALUES('Table'[Invoice])

kristel_tulio_0-1685410175904.png

 

 

Hi @kristel_tulio ,
My goal is to achieve the following in a blue square. 

 

GeorgeCs_1-1685417432785.png

 

I have applied your suggestion based on the VALUES('Table'[Invoice]) please refer above, it has summarised all the listed invoice totals excl gst based on the # of Line items. My goal is to get the total value of all invoices (blue square) correct. As its is picking or summarise only the max value to the duplicate invoice. Only I could think to use the ID to be as additional unique value (top of the invoice number) to get the Totals correct.
I hope it helped to provide more inf.

Thank you for your suggestion.

G.   

@GeorgeCs ,

You can try this as well,

Total Invoice Excl GST =
var invoice =
SUMMARIZE(
    'Table', 'Table'[Transaction ID], "Total Invoice Excl New", MAX('Table'[Invoice Total Excl GST])
)
return
SUMX(
    invoice, [Total Invoice Excl New])
kristel_tulio_1-1685422582726.png

 


 

 

Thank you. Yours worked as well. 
I found out that more analysis needs to be done at my end. 😉 

 

G

@GeorgeCs 
Summarize GST =
SUMX ( VALUES ( 'Table CV'[GST] ), 'Table CV'[GST] )

Mahesh0016_0-1685422115310.png

@GeorgeCs I hope this helps you!!THANK YOU!!

Thank you it did help. 
However, after applying the measure, I found out that more analysis needs to be done at my end. 😉 

Cheers

G

Hi @GeorgeCs 

Have you tried putting the ID in the VALUES function instead of the Invoice? Did you get your desired result?

Also, do you just need to add the MAX value of each invoice then add them?

GeorgeCs
Frequent Visitor

Hi @Mahesh0016,
After posting it I did realise that I should have add more inf. Sorry for that. I am new to the community. 😉

 

Once applying the above DAX measures and I have created the Table visual (pls. refer below) the total does not add up. And it does impact the other visuals totals.

 

GeorgeCs_0-1685405418248.png

I hope it would make sense.
Thanks
G.

Mahesh0016
Super User
Super User

@GeorgeCs Please Share Your ENDOUT in table for more understanding.THANK YOU!!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors