Forum Discussion
Sum the Values in Table B IF Table A Meets Criteria
Community ChampionPlease see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490.
Need example data for this one.
I changed my data structure by appending B data to A data in order to create one table that has two columns, one for A sales and one for B sales. I want to write a formula that will sum A sales if B sales for the employee are greater than 0. It should be dynamic so that if I change the month, the formula will calculate only for that month.
The Employee ID table is connected to the sales Table through 'Employee ID'
| Employee ID | Month | A Sales | B Sales | Company |
| 1 | 1 | 10 | 0 | A |
| 1 | 2 | 20 | 0 | A |
| 1 | 3 | 10 | 0 | A |
| 2 | 2 | 5 | 0 | A |
| 3 | 6 | 20 | 0 | A |
| 3 | 9 | 25 | 0 | A |
| 4 | 2 | 10 | 0 | A |
| 4 | 7 | 10 | 0 | A |
| 4 | 9 | 10 | 0 | A |
| 4 | 10 | 5 | 0 | A |
| 5 | 2 | 10 | 0 | A |
| 5 | 3 | 30 | 0 | A |
| 5 | 6 | 25 | 0 | A |
| 3 | 2 | 0 | 10 | B |
| 3 | 6 | 0 | 20 | B |
| 4 | 7 | 0 | 10 | B |
| 4 | 10 | 0 | 15 | B |
| Employee ID |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
v-yuta-msftCommunity Support
Hi dgenatossio,
Your requirement is to create a slicer based on Table[Month] column and create a measure using DAX like this:
Result = CALCULATE(SUM(Table1[A Sales]), ALLSELECTED(Table1[Month]), Table1[B Sales] > 0)
Regards,
Jimmy Tao
- Ashish_Mathur
Super User
Hi,
Based on the data that you have shared, what exact result are you expecting?
Employee ID Month A Sales B Sales Company 1 1 10 0 A 1 2 20 0 A 1 3 10 0 A 2 2 5 0 A 3 6 20 0 A 3 9 25 0 A 4 2 10 0 A 4 7 10 0 A 4 9 10 0 A 4 10 5 0 A 5 2 10 0 A 5 3 30 0 A 5 6 25 0 A 3 2 0 10 B 3 6 0 20 B 4 7 0 10 B 9 6 0 15 B This data makes it easier to explain. I would expect the grand total to be 40 (which is B sales, except for employee 9 which doesn't have A sales >0). The problem is that for the Grand Total it sees that Total A Sales are >0 so it returns 55 (which is all of B sales). I need the grand total to be 40 since only employees 3 and 4 have A Sales >0.
My employee table:
Employee ID 1 2 3 4 5 9
