Forum Discussion
SUM function with multiple condition
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
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.
3 Replies
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.
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
Hi,
If for id 123, there was one TRUE and one FALSE, then would 150 be considered?
