Sum Distinct values based on string values

[powerbiss]

Hi Team,
I am trying to sum distinct values from amount_total column based on state and invoice_status columns. State should be "draft" and invoice_status should be "no"

Answer: 200000

Thanks for all your help

[powerbiss]

Hi John,

Really appreciate your help in solving my problem. What is TREATAS.

Your solution worked great.

Thanks again

[johnt75]

TREATAS is a means of moving or creating filters. If you want to filter a number of columns by different values then you can create a table using {} table constructor and then use TREATAS to say this column of this table matches the first value, this column of that table matches the 2nd value etc.

Also handy if you want to move an existing filter from one table to another, e.g. TREATAS(VALUES('Table1'[id]), 'Table2'[id])

[johnt75 · Accepted solution]

create a measure like

Sum of distinct values =
var distinctAmounts = CALCULATETABLE(VALUES('Table'[amount_total]), 
TREATAS( { ( "draft", "no") }, 'Table'[state], 'Table'[invoice_status])
)
return SUMX(distinctAmount, [amount_total])