topic Re: Dax Sum Unique ID values ones in DAX Commands and Tips

Dax Sum Unique ID values ones

casperlow — Wed, 28 Aug 2024 07:43:47 GMT

Hi Dax Guru's
How do I perform a dax measure to calculate the sum of the cost ones for each unique ID.
My current Dax formula: Total Cost = sum(cost) but the output is 1100 which is incorrect.
The correct output should show 600 only

See image below

Appreciate any input on this.
Did my research but could not find any solution that was close enough.

Re: Dax Sum Unique ID values ones

Gabry — Wed, 28 Aug 2024 07:51:28 GMT

Hello casperlow ,
if every project has just one value, you can use max with summarize:

Total = SUMX(SUMMARIZE('Table','Table'[project id], "val", MAX('Table'[unit])),[val])

Let me know if this helps

Re: Dax Sum Unique ID values ones

casperlow — Thu, 29 Aug 2024 06:15:13 GMT

It doesn't work when I tried this Dax on creating a measure, the error im gettings is "The end of the input was reached."
I had to tweak it a little for it to make it work on my table, I had to add a filter but overall the dax formula works. 🙂 thank you

Re: Dax Sum Unique ID values ones

Irwan — Thu, 29 Aug 2024 05:08:18 GMT

hello casperlow

is this what you are looking for?

create a new measure with following DAX.

Total Sum = 
var _Cat = SELECTEDVALUE('Table'[Category])
Return
SUMX(
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Category]=_Cat
    ),
    'Table'[Unit]
)

however, if you dont put a selection in slicer, it will become blank.

While put them into table will show all the value.

Hope this will help.

Thank you.

Re: Dax Sum Unique ID values ones

casperlow — Thu, 29 Aug 2024 06:14:28 GMT

yeah tried this it works on a table but not a on card. preferably works on both. the above solution worked but I had a filter for it to work.