Forum Discussion
Sum column by group based on another column containing a value
Hello All,
I have the data table below where I would like to sum the revenue by job name if the item number "2222" is present.
| Job Name | Item Number | Revenue |
| Project 1 | 1111 | $ 50,000 |
| Project 1 | 2222 | $ 40,000 |
| Project 2 | 1111 | $ 60,000 |
| Project 2 | 2222 | $ 55,000 |
| Project 2 | 3333 | $ 10,000 |
| Project 3 | 1111 | $ 30,000 |
The result should be $215,000 (all rows except the last for this simplified example).
I've tried using the measure below, however it only sums the revenue for the item number "2222" and not the total job revenue.
Job Revenue by 2222 =
CALCULATE (
SUM('Table Name'[Revenue]),
ALLEXCEPT('Table Name','Table Name'[Job Name]),
FILTER('Table Name', FIND ( "2222", 'Table Name'[Item Number],, 0 ) <> 0 )
)
I understand why the code above does not work, but I cannot seem to figure out how to manipulate this in the way that I need for the measure to work properly.
I would greatly appreciate any assistance in tuning this up, thank you.
geotech I will this improved measure
Sum Job 2 = VAR __jobs = CALCULATETABLE ( VALUES ( Job[Job Name] ), Job[Item Number] = "2222" ) RETURN CALCULATE ( SUM ( Job[Revenue] ), __jobs )Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
4 Replies
- parry2k
Super User
geotech try this measure
Sum Job = CALCULATE ( SUM ( Job[Revenue] ), FILTER ( SUMMARIZE ( FILTER ( Job, Job[Item Number] = "2222" ), Job[Job Name], "@Count", COUNTROWS ( Job ) ), [@Count] > 0 ) )Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
- parry2k
geotech I will this improved measure
Sum Job 2 = VAR __jobs = CALCULATETABLE ( VALUES ( Job[Job Name] ), Job[Item Number] = "2222" ) RETURN CALCULATE ( SUM ( Job[Revenue] ), __jobs )Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
- parry2k
geotech glad I could help and it worked.
Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
