Grouping Measure counting value only once per group

[domtrump]

I have data structured as such:

An Account can have multiple records in the same category. In such cases, their Revenue will be listed as the same value for each record. I need a MEASURE that will only sum the Revenue value ONE time per Account-Category combination. So in my example, Account 108392299 has two (2) records for the New category. Each record pulls in his revenue of $201.35. My resulting measure SummarizedRevenue, should only count the $201.35 ONE time because it is the same Account-Category combination. How can I construct such a measure?

[vicky_ · Accepted solution]

If possible, i'd recommend going back to PowerQuery and just removing duplicate rows (unless there's a particular reason you need them?)
Otherwise, try a measure like: 

Amt per Group = 
var tempTable = SUMMARIZE('Table', 'Table'[Account], 'Table'[Catg], 'Table'[Amt])
return SUMX(tempTable, 'Table'[Amt])

[FreemanZ · Accepted solution]

hi @domtrump ,

try to plot with a measure like:

Sum = 
SUMX(
    SUMMARIZE(data, data[Account], data[Category], data[Revenue]),
    data[Revenue]
)

it worked like:

[vicky_ · Accepted solution]

If possible, i'd recommend going back to PowerQuery and just removing duplicate rows (unless there's a particular reason you need them?)
Otherwise, try a measure like: 

Amt per Group = 
var tempTable = SUMMARIZE('Table', 'Table'[Account], 'Table'[Catg], 'Table'[Amt])
return SUMX(tempTable, 'Table'[Amt])