Measures based on table containing duplicates

[Lew_14]

Hello,

A bit of background - the data i'm working with shows information relating to audits and there is one row of data for each question in the audit. The 'PublishedRecordID' field shows the audit each question relates to but the 'Total Score' and 'Total Possible Score' fields relate to the overall audit score, not the score for that question (data example and expected result below).

Data Example (Table Name - Audits)				Expected Result
PublishedRecordID	Total Score	Total Possible Score		PublishedRecordID	Score (%)
1	5	10		1	50.00%
1	5	10		2	0.00%
1	5	10		3	80.00%
2	0	23
2	0	23
2	0	23
3	16	20
3	16	20

I'm trying to work out the percentage score at audit level and have so far achieved this by taking a copy of the table and removing all duplicates based on the 'PublishedRecordID', which works fine in terms of calculating the score. It's becoming more troublesome using this method as there are a multiple slicers in the report that don't always work across both of the tables (the original and the de-duped version). I've attempted to calculate the score in my original table (ignoring any duplicates) but it's giving me slightly different scores to those from the de-duped version and i can't quite work out why. It's a simple calculation and the DAX i'm using in the table containing duplicates is below.

Score % = (CALCULATE(SUMX(Audits,Audits[Total Score]),Audits[PublishedRecordId]))/(calculate(SUMX(Audits,Audits[Total possible Score]),Audits[PublishedRecordId]))

Any help or guidance would be appreciated!

Thanks

[SpartaBI · Accepted solution]

@Lew_14 not sure if that's what you meant but I get the result you want:

Measure = 
IF(
    HASONEVALUE('Table'[PublishedRecordID]),
    DIVIDE(SUM('Table'[Total Score]), SUM('Table'[Total Possible Score]))
)

Showcase Report – Contoso By SpartaBI