Forum Discussion

vin123's avatar
vin123
Frequent Visitor
6 years ago
Solved

average values then sum based on a unique ID

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:

 

FacultyCountofPaperIDSum of Citations
Arts420
Science411

 

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

2 Replies