topic Sum the Unique values to appropriate value in DAX Commands and Tips

Sum the Unique values to appropriate value

Kopec — Fri, 31 Jan 2025 08:52:14 GMT

Can anybody help me with this:
Sum the unique values in column "B" to the appropriate value in column "A".

Thank you,

Jan

Re: Sum the Unique values to appropriate value

DataNinja777 — Fri, 31 Jan 2025 09:19:22 GMT

Hi Kopec ,

You can achieve this in Power BI using DAX with a measure that sums the unique values of Column B for each corresponding value in Column A. The following DAX measure accomplishes this:

Result = SUMX ( VALUES ( 'Table'[Column B] ), 'Table'[Column B] )

This measure works by extracting the unique values in Column B for each group in Column A using VALUES('Table'[Column B]), then iterating over these unique values and summing them with SUMX(...). If you prefer to do this transformation in Power Query, you would first remove duplicates from Column B while keeping only unique values for each group in Column A. Then, you would group by Column A and sum the unique values in Column B. Finally, merge this result back to the original table to display the calculated sum in a new column.

Best regards,

Re: Sum the Unique values to appropriate value

Kopec — Fri, 31 Jan 2025 11:54:07 GMT

It's almost done, but I need:

SUMX ( VALUES ( 'Table'[Column B] ), 'Table'[Column A] )

In Column "A" is string.... I have got a mistake. I suppose because of that STRING values in Column A.
Can you help me?

Re: Sum the Unique values to appropriate value

mark_endicott — Fri, 31 Jan 2025 12:55:08 GMT

Kopec - You can use the below to get your desired answer (just change the column names):

VAR _table = SUMMARIZE( 'Table', 'Table'[Column1], 'Table'[Column2] ) RETURN SUMX( _table, 'Table'[Column2])

If this works for you please accept as the solution to help others with the same challenge.

Re: Sum the Unique values to appropriate value

Kopec — Fri, 31 Jan 2025 13:08:46 GMT

Hi Mark,
ist look great, but I need to get value for every row in the table. According to the picture (column "RESULT"):

Re: Sum the Unique values to appropriate value

mark_endicott — Mon, 03 Feb 2025 10:12:56 GMT

Kopec - As far as I'm aware this will be nearly impossible in a measure due to the way Power BI groups identical rows in it's visualisations. Even if you add an index column to undo the grouping, there will still be an issue with the way we have to use SUMMARIZE to remove the row duplication to get the result you want.

There may be a solution out there for you but I've tried a few, including adding a duplicate of the table, and none seem to work.

I suggest you think about why it is necessary to present your data in this way, and if the solution I have provided works, or use Excel.

Re: Sum the Unique values to appropriate value

Anonymous — Mon, 03 Feb 2025 05:45:48 GMT

Hi,

Thanks for the solution mark_endicott and DataNinja777 offered, and i want to offer some more information for user to refer to.

hello Kopec , mark_endicott 's solution is right, you can use it as a calculated column .

Column = VAR a = SUMMARIZE ( 'Table', [Column1], [Column2] ) RETURN SUMX ( FILTER ( a, [Column1] = EARLIER ( 'Table'[Column1] ) ), [Column2] )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Sum the Unique values to appropriate value

mark_endicott — Mon, 03 Feb 2025 10:28:47 GMT

Anonymous - Thank you, I always forget about calculated columns! Can I request we mark my solution please as this one is solved!

Re: Sum the Unique values to appropriate value

Kopec — Mon, 03 Feb 2025 20:47:28 GMT

Hi,

for Power BI it works great.

Can anybody help me how to make it work in Power Pivot?
And can anybody know, why the below formula doesnt work?

=CALCULATE (SUMX(TABLE; Column B);VALUES(Column B); ALLEXCEPT(TABLE; Column A)

)

Re: Sum the Unique values to appropriate value

Anonymous — Tue, 04 Feb 2025 09:09:52 GMT

Hi Kopec

The formula i offered can work in power pivot.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.