How do I exclude duplicate values when I sum the revenue within a column and apply to a Card.

[james_wood]

I have a table with 3 columns.

Column A contains unique IDs to various projects. 

Column B contains the revenue for each project.

Column C contains a category that means some projects in the table appear twice because they fit multiple categories. 

I have inserted a Card onto the page which displays total revenue for all projects, which is summing the total of Column B. However because of column C several of these projects are listed twice and so their revenues are counted twice which is not what i want.

I want to calculate the sum of revenue for all unique projects. I do not want to delete rows with duplicate values as other areas of my report utilise these duplicate values so it's important they remain. I simply want to sum the revenues of Column B but leaving out values where duplicates of Column A are found. Similar to how I can use the 'Count (Disctinct)' feature for unique number, I'd like to find a similar 'Sum (Distinct)' option but as per the screenshot below this doesn't exist.

[jdbuchanan71]

@james_wood 

This is not a new table and should not be added as a calculated column.  It should be added as a measure and you would put the measure in your card.  You just have to change the name of the table and the columns to match your data.

[james_wood]

Thanks! It worked. Still not sure how the GroupBy function works exactly but it does the job. Will continue the googling to understand 😄

[jdbuchanan71 · Accepted solution]

@james_wood 

We can use SUMX over a GROUPBY that includes the right columns to get to what you are looking for.

Revenue Corrected = 
SUMX ( 
    GROUPBY ( YourTable,YourTable[Project ID], YourTable[Revenue]),
    YourTable[Revenue]
)

[james_wood]

Many thanks @jdbuchanan71 for the quick response. I should clarify I don't have or need a table in my report. It is just a Card.

I've added a new column in the imported table field and copied your code but replacing with the relevant column names for ID, revenue. I just get the same huge revenue number in every row of the column (tens of $billions). Note even when I sum all values including duplicates I get abour $1Billion. As I don't know how the GROUPBY function works (a quick google doesn't give straightforward results) any further ideas?