Forum Discussion
Need help adding unique values across multiple columns
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 |
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.
7 Replies
Updated to show better picture of table
Hello Dangar, I need the actual measure
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.
please put what output you nedd from above data you provide
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.
