Sum of Distinct Values in DAX

[Hoping]

My data looks like below:

Person ID	Current Year	Past Years	Category	Contract Code	Person_Year_Points
1	2024	2022	M1	A	2
1	2024	2022	M2	A	2
1	2024	2022	M3	A	2
1	2024	2023	M1	B	2.5
1	2024	2023	M2	B	2.5
1	2024	2023	M3	B	2.5
1	2024	2024	M1	C	1
1	2024	2024	M2	C	1
1	2024	2024	M3	C	1
2	2024	2023	M1	A	4
2	2024	2023	M2	A	4
2	2024	2024	M1	A	0
2	2024	2024	M2	A	0

Person_Year_Points repeats itself for PersonID and Year Combinations. And that's because Category Code varies for each year.  Since Person_Year_Points  repeats itself, I need to get the distinct Person_Year_Points for each Person ID and Year Combination (excluding 2024) and sum them and add as a column to the dataset I am generating using DAX.

 Person 1:

2022 value = 2

2023 value = 2.5

Sum (excluding 2024) = 4.5

Person 2:

2023 value = 4

Sum (excluding 2024) = 4

The output needs to look like below.  The entire table visual is filtered for 2024. But the last column that Sums Point needs to be for prior years.

Person ID	2024 Category	2024 Contract Code	Sum of Person Year Points (exclude 2024)
1	M1	B	4.5
1	M2	B	4.5
1	M3	B	4.5
2	M1	A	4
2	M2	A	4

I need the measure to be simple as it is a large dataset.  The measure will be defined inside a DAX query that will generate the above dataset. I am using a table visual to generate DAX query.

[v-xianjtan-msft · Accepted solution]

Hi @Hoping 

Try creating a measure like this:

Person Year Points (exclude 2024) = 
CALCULATE(
    SUMX(
        DISTINCT(
            SELECTCOLUMNS(
                FILTER(
                    'DataTable',
                    'DataTable'[Past Years] <> 2024
                ),
                "Person ID", 'DataTable'[Person ID],
                "YearPoints", 'DataTable'[Person_Year_Points]
            )
        ),
        [YearPoints]
    ),
    ALLEXCEPT('DataTable', 'DataTable'[Person ID])
)

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

[Rakesh1705]

Step 1=
find the points where current year is not equal to past years

Step 2= calculate the maximum value of contract code

Step 3= calculate the sum of person points for the past years which are not equal to current year

Final Visual

if this process is meeting your expectation then please accept the same as your solution

[Hoping]

Hi @Rakesh1705   Thank You. Why are we doing a MAX Contract Code ? The output needs to hold 2024 Contract Code for the person, Person 1 it is C.  Person 2 it is A.   I am now adding a filter to the table visual to include Past Years = 2024. Only the Sum_Person_Points will be sum of past years excluding 2024. Other columns will be for 2024 only.

[DataNinja777]

Hi @Hoping ,

There are multiple ways to achieve your desired output, and one of them is by writing a table DAX formula like the one below:

Table 2 = 
FILTER(
    SUMMARIZE(
        'Table',
        'Table'[Person ID],
        'Table'[Category],
        'Table'[Contract Code],
        "Person Year Points (exclude 2024)", 
        CALCULATE(
            SUM('Table'[Person_Year_Points]),
            'Table'[Past Years] <> "2024"
        )
    ),
    [Person Year Points (exclude 2024)] <> BLANK()
)

The resulting output is shown below:

I am attaching an example pbix file.  

Best regards,

[Hoping]

Hi @DataNinja777  Thank You. I created a measure that includes the CALCULATE part of your code that does sum for past years <> 2024 but it never returns when I add it to the visual. It keeps circling until I remove the measure from the table. The dataset is medium sized but wide and has many columns pulled in from dimensions and all columns are for 2024 as the table visual is filtered to Past Year = 2024.

Is there another way to do it which is less compute intensive? SUMX over Staff ID and Year Combinations?