SUM function with multiple condition

[auron]

Dear all, i have a table like this one:

id	price	requested
123	150	TRUE
123	150	TRUE
456	100	TRUE
456	100	TRUE
789	200	TRUE
789	200	TRUE
484	350	TRUE
159	280	TRUE
753	420	FALSE
Total	150+100+200+350+280
	1080

What i need is a DAX query to calculate a new measure for the sum of the column price based on these conditions:

• Row must have value "true" for column "requested"
• I have multiple rows with same ids, i need to consider only once rows with the same ID (distinct ids)

The total should be calculated like this:
Total = 150 (id:123) + 100(id:456) + 200 (id:789) + 350 (id:484) + 280 (id:159)

Can you please help me? Thanks a lot

[Ashish_Mathur]

Hi,

If for id 123, there was one TRUE and one FALSE, then would 150 be considered?

[v-yueyunzh-msft]

Hi , @auron 

According to your description, you want to ”SUM function with multiple condition“.

Here are the steps you can refer to :
(1)My test data is the same as yours.

(2)We can click "New Measure" to create a measure:

Measure = 
var _t2= SUMMARIZE(FILTER('Table','Table'[requested]=TRUE()), [id] ,"price" , MAX([price]))
return
SUMX(_t2 ,[price])

Then we can meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[MFelix · Accepted solution]

Hi @auron ,

Try the following code:

Total =
SUMX (
    SUMMARIZE (
        FILTER ( 'Table', 'Table'[requested] = "TRUE" ),
        'Table'[id],
        'Table'[price]
    ),
    'Table'[price]
)

Be aware that if you have different values for the price in each ID you get the value twice depending on the option you need you may need to adjust this to the following:

Total =
SUMX (
    SUMMARIZE (
        FILTER ( 'Table', 'Table'[requested] = "TRUE" ),
        'Table'[id],
        "MAXIMUMPRICE", MAX ( 'Table'[price] )
    ),
    [MAXIMUMPRICE]
)

You can then adjust the MAX to minimum, average whatever value you need if the prices are different.