Search code examples
excelpivot-tablemeasure

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.

Solution

  • 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.