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

[GeorgeCs]

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

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.

[kristel_tulio]

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

[GeorgeCs]

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

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.   

[kristel_tulio]

@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])

[GeorgeCs]

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

G

[Mahesh0016 · Accepted solution]

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

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

[GeorgeCs]

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

[kristel_tulio]

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]

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.

I hope it would make sense.
Thanks
G.

[Mahesh0016]

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