Need help adding unique values across multiple columns

[Userpath77]

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

[v-xiandat-msft · Accepted solution]

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.

[Userpath77]

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.

[Dangar332]

please put what output you nedd from above data you provide

[Userpath77]

Hello Dangar, I need the actual measure

[Dangar332]

hi, @Userpath77 

try below

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

[Userpath77]

Updated to show better picture of table

[Dangar332]

hi, @Userpath77 
you want seprate table or measure?