average values then sum based on a unique ID

[vin123]

Hi all. I am trying to get a sum of citations in a faculty but for de-duplicated paperIDs. Here is my dummy data:

As you can see, one faculty can have multiple authors to a paper ID. But I don't want to double count citations if it's for the same paper ID, therefore I want to either exclude the Author column or somehow fix the paper ID column as unique.

I'm coming from Tableau so please bare with me as this is the way I'd do it in Tableau:

 {FIXED [Paper ID]: AVG([Citations])}   -- and then SUM this Measure when dragged to the graph.

Here is how I'd like the data to look:

Faculty	CountofPaperID	Sum of Citations
Arts	4	20
Science	4	11

But what I'm getting is the Arts faculty with 30 citations, because it's counting PaperID1001 twice. 

I'm struggling to find the dax calculation to average the citations by the paper ID, then take the SUM. Or fix the paper ID somehow? Thank you

[amitchandak · Accepted solution]

Try Like

Sum of Citations= sumx(summarize(table, table[Faculty], table[PaperID],"_Citations" ,max(Citations)),[_Citations])
CountofPaperID = distinctcount(table[PaperID])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[vin123]

this works great, thankyou!