Forum Discussion

dgenatossio's avatar
dgenatossio
Frequent Visitor
8 years ago

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

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support

    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's avatar
      dgenatossio
      Frequent Visitor

      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?

      • v-yuta-msft's avatar
        v-yuta-msft
        Icon for Community Support rankCommunity Support

        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

    • dgenatossio's avatar
      dgenatossio
      Frequent Visitor

      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
      • v-yuta-msft's avatar
        v-yuta-msft
        Icon for Community Support rankCommunity 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