cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dgenatossio
Frequent Visitor

Sum the Values in Table B IF Table A Meets Criteria

I have 3 tables. One is a table with employee IDs (Table 'ID'). One is a table with employee IDs and their 2017 sales for one business (Table 'A'). The third table has employee IDs and their 2017 sales for the other business (Table 'B'). Tables A and B are connected to the employee table by the employee ID. The employees are not exactly the same for each business, though there is some overlap where the employee ID could appear on A and B. I have columns on the ID table that are used as filters on the page, based on where the employee is located and how long they have been at the company.

 

I want to calculate the overlapping 2017 results. I have created a measure on the ID table that says this:

 

Overlap = if(SUM('A'[Sales])>0,SUM('B'[Sales]),0)

 

Basically: if the employee had Sales for A, give me their sales for B

 

It works for each ID on the table and the row shows 0 for the row if sales for A were 0, but the Grand Total returns the entire Sales for B (not just the sum of the sales for IDs that had more than 0 sales for A).

 

Any ideas on how to get the total to work correctly?

10 REPLIES 10
dgenatossio
Frequent Visitor

Re: Sum the Values in Table B IF Table A Meets Criteria

Can anyone help with this?

Highlighted
Community Support
Community Support

Re: Sum the Values in Table B IF Table A Meets Criteria

Hi dgenatossio,

 

Create a measure like this pattern and try again.

Overlap =
CALCULATE ( SUM ( B[sales] ), FILTER ( A, RELATED ( A[Sales] ) > 0 ) )

Regards,

Jimmy Tao

dgenatossio
Frequent Visitor

Re: Sum the Values in Table B IF Table A Meets Criteria

Jimmy, the 'Related' function does not show the column 'sales' from table A - there is no direct connection between A and B, they are both connected to 'ID'.

 

Any work around?

Community Support
Community Support

Re: Sum the Values in Table B IF Table A Meets Criteria

Hi dgenatossio,

 

In dax, many side can't be used to filter one side between two tables, you should change the table structures.

 

Regards,

Jimmy Tao

Super User IV
Super User IV

Re: Sum the Values in Table B IF Table A Meets Criteria

Please 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.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

dgenatossio
Frequent Visitor

Re: Sum the Values in Table B IF Table A Meets Criteria

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 IDMonthA SalesB SalesCompany
11100A
12200A
13100A
2250A
36200A
39250A
42100A
47100A
49100A
41050A
52100A
53300A
56250A
32010B
36020B
47010B
410015B

 

 

Employee ID
1
2
3
4
5
Community Support
Community Support

Re: Sum the Values in Table B IF Table A Meets Criteria

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)

捕获.PNG 

 

Regards,

Jimmy Tao

Super User IV
Super User IV

Re: Sum the Values in Table B IF Table A Meets Criteria

Hi,

 

Based on the data that you have shared, what exact result are you expecting?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
dgenatossio
Frequent Visitor

Re: Sum the Values in Table B IF Table A Meets Criteria

Employee IDMonthA SalesB SalesCompany
11100A
12200A
13100A
2250A
36200A
39250A
42100A
47100A
49100A
41050A
52100A
53300A
56250A
32010B
36020B
47010B
96015B

 

 

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. 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors