Summing a filtered version of a table, using only the unique values (Virtual Tables Question?)

[mmace1]

The situation is easy to solve via Power Query, but I'd love to know how to do this in DAX. 

Say I have the below table:

I want to sum the Spend column: SUM(Spend)

Only when RuleviolationID = 7:  CALCULATE(SUM(SPEND), RuleViolationID = 7)

So that's all very simple. 

But see how the PaymentIDs have duplicates?  I only want to sum the Spend 1x per each instance of a PaymentID. 

I was thinking of using CALCLUATETABLE to pull a version of the table where RuleViolationID = 7, And then using VALUES to only grab unique values of the PaymentID, but - obviously that doesn't work.  It makes me think virtual tables would be the way to go, though. 

It's easy to just pull a custom version of the table in Power Query, but is there a good way to work this in DAX? 

[v-cherch-msft]

Hi @mmace1 

You may try to create a table with SUMMARIZE Function. For example:

Table =
SUMMARIZE (
    FILTER ( Table1, Table1[RuleViolationID] = 7 ),
    Table1[PaymentID],
    "Spend", SUM ( Table1[Spend] )
)

Regards,

Cherie

[mmace1]

Thanks - wouldn't summarize by the paymentID, result in duplicate payment IDs being counted more than 1x though?