Excel Pivot Table and Measures - How Can I have Distinct Count of IDs and Distinct Sum of related values

ID	Amount State Category
1	10 MyState MyCategory1
1   10 MyState MyCategory2
1	10 MyState MyCategory3
2	20 MyState MyCategory1
2	20 MyState MyCategory2
3	30 MyState MyCategory1

Here is my Excel table.

I use a Pivot Table to show totals per state, and I use DISTINCT COUNT of ID to show how many are in each category.

The problem is that the SUM of the Amount doesn't match up with the number in the DISTINCT COUNT of ID column.

How can I create a measure for the Pivot Table so that I can have an effective DISTINCT SUM of Amount (relating to ID)?

Thank you.

You can use the DAX language in Power Pivot with the SUMX and DISTINCT functions.

Power Pivot tab > Manage Data Model > Home > New Measure

Use the following formula, replacing MyTableName:

DistinctSumAmount := SUMX(DISTINCT('MyTableName'[ID]), 
CALCULATE(SUM('MyTableName'[Amount])))

Save and go back to your Excel workbook and create a Pivot Table using the data model and add the new DistinctSumAmount measure to your Pivot Table.

It's difficult to understand what you want because your example data set has distinct combinations of ID and Category, so even when you take a distinct count and a distinct sum, it would give the same values as an absolute count and absolute sum.

Also, a distinct sum based on ID only makes sense if each ID has a fixed Amount. If there are different rows where the ID is the same but the Amount is different, then it's not clear what a distinct sum should be.

So, using different example data where there are duplicate IDs for the same Category, but always with the same Amount for each ID, here's a potential solution:

=COUNTIFS($A$2:A2,A2,$D$2:D2,D2)=1

Step 1 is to add a helper column that identifies whether each row contains the first unique combination of ID and Category. It does this by counting from the first row down the current row (using a range that is fixed to start from $A$2 but extends to A2, so that it expands as the formula fills down).

Then add a pivot table as normal but set the First column to be used as a page filter, and filter it to TRUE. This then allows you to take a count of the ID column and a sum of the Amount column, and it only includes each unique combination once.