Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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:
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
Solved! Go to 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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi,
If for id 123, there was one TRUE and one FALSE, then would 150 be considered?
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 @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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
84 | |
66 |