topic Re: Need help adding unique values across multiple columns in DAX Commands and Tips

Need help adding unique values across multiple columns

Userpath77 — Wed, 08 Nov 2023 19:00:00 GMT

Hi All

I have a table like below that has 5 columns and each row has a unique combination (Fund, Item, Line, Year). I need a dax formula that will look at an entire table and sum the amounts for each unique combination (ex: Fund: Amex, Item: 2, Line: 2020, Year: 2023). My real dataset has a few thousand rows.

Can anyone help with this?

FUND	ITEM	LINE	YEAR	AMOUNT
AMEX	2	2020	2022	15000
CAP	1	1954	2022	12000
AMEX	2	2020	2022	7000
CAP	1	1954	2023	900
AMEX	2	2020	2023	10000

Re: Need help adding unique values across multiple columns

Userpath77 — Wed, 08 Nov 2023 19:02:28 GMT

Updated to show better picture of table

Re: Need help adding unique values across multiple columns

Dangar332 — Wed, 08 Nov 2023 19:33:01 GMT

hi, Userpath77
you want seprate table or measure?

Re: Need help adding unique values across multiple columns

Userpath77 — Wed, 08 Nov 2023 19:45:43 GMT

Hello Dangar, I need the actual measure

Re: Need help adding unique values across multiple columns

Dangar332 — Wed, 08 Nov 2023 19:49:37 GMT

hi, Userpath77

try below

measure 6= sum('tablename'[amount])

Re: Need help adding unique values across multiple columns

Userpath77 — Wed, 08 Nov 2023 20:13:34 GMT

Thanks but what I need is somthing that will find and add each combination. As an example, since both Amex Lines have the same values for fund, item, line, year, the measure should show 1 total that equals both lines. I need that type of formula that will find matching combinations like decribed above and total them.

Re: Need help adding unique values across multiple columns

Dangar332 — Wed, 08 Nov 2023 20:18:24 GMT

please put what output you nedd from above data you provide

Re: Need help adding unique values across multiple columns

Anonymous — Mon, 27 Nov 2023 02:24:59 GMT

Hi Userpath77 ,

Below is my table:

The following DAX might work for you:

Measure = CALCULATE( SUM('Table'[Amount]), ALLEXCEPT('Table','Table'[Line]) )

The final output is shown in the following figure:

Best Regards,

Xianda Tang

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