topic Re: distinct add with repeated values in DAX Commands and Tips

distinct add with repeated values

JuanPabloCarr — Wed, 08 Feb 2023 15:54:57 GMT

Hi, i got the following problem:

We have purchase orders for materials, these are received little by little by the suppliers, so repeated rows of the same order are created as the materials are received as follows:

\|Purchase order\|	\|product\|	\|quantity order\|	\|recibed\|	\|money\|
1	wood	30	10	100$
1	wood	30	20	100$
2	door	20	20	50$
3	window	15	10	300$
3	window	15	5	300$

How can I sum the amount of money without repeating the amounts depending on the purchase order?

Re: distinct add with repeated values

FreemanZ — Wed, 08 Feb 2023 15:58:21 GMT

hi JuanPabloCarr

try like:

Measure =

VAR _table =

ADDCOLUMNS(

VALUES(TableName[Purchase order]),

"Money",

CALCULATE(MAX(TableName[Money]))

)

RETURN

SUMX(_table, [Money])

Re: distinct add with repeated values

JuanPabloCarr — Wed, 08 Feb 2023 16:21:52 GMT

it isnt working, the code return the same value as if i sum all the values of the column "Money", counting the repeated ones

Re: distinct add with repeated values

smpa01 — Wed, 08 Feb 2023 16:59:15 GMT

JuanPabloCarr what is the desired end goal based on the sample data

Re: distinct add with repeated values

JuanPabloCarr — Wed, 08 Feb 2023 17:10:26 GMT

i need a measure to sum 450 (1wood + 1door + 1windows) and not 850 (2wood + 1door +2windows)

Re: distinct add with repeated values

smpa01 — Wed, 08 Feb 2023 17:36:34 GMT

JuanPabloCarr

Measure = CALCULATE(SUMX(VALUES(tbl[|money|]),tbl[|money|]),ALL(tbl))

Re: distinct add with repeated values

JuanPabloCarr — Wed, 08 Feb 2023 17:56:13 GMT

That works for me, thanks!