topic Re: DAX Measure to summarise only unique invoice # totals in curated dataset. in DAX Commands and Tips

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

GeorgeCs — Mon, 29 May 2023 03:41:32 GMT

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:

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

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

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

Mahesh0016 — Mon, 29 May 2023 11:13:29 GMT

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

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

GeorgeCs — Tue, 30 May 2023 00:22:00 GMT

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.

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

kristel_tulio — Tue, 30 May 2023 01:32:48 GMT

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

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

GeorgeCs — Tue, 30 May 2023 03:38:40 GMT

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.

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

kristel_tulio — Tue, 30 May 2023 04:15:34 GMT

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?

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

Mahesh0016 — Tue, 30 May 2023 04:49:21 GMT

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

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

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

kristel_tulio — Tue, 30 May 2023 04:56:28 GMT

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

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

GeorgeCs — Tue, 30 May 2023 08:50:01 GMT

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

Cheers

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

GeorgeCs — Tue, 30 May 2023 08:52:05 GMT

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