Forum Discussion

Velvetine's avatar
Velvetine
New Member
4 years ago
Solved

Excluding data from one table and adding from another

Hi all,

 

I have this sales table that have many outlets, which one of them is our online store. However, for online store this table isn't particularly accurate. So we have this another sales table which is only for online store. Is there a way for me to combine the table ?

 

Table A :

DateOutletSales
1/1/2022ABC100
1/1/2022Online Store250
1/1/2022DEF150

 

Table B :

DateOutletSales
1/1/2022Online Store150
1/1/2022Online Store200

 

I tried this : 

 

CALCULATE(SUM(Table A[Sales]), FILTER(Outlet<>"Online Store") + CALCULATE(SUM(Table B[Sales]))

 

I notice the formula above doesn't work when i try to visualize the sales based on outlet. For some reason the sales from Table B will add on to all the outlet instead of making it part of outlets


Appreciate your help on this matter.


Thanks !

 

DAX
  • amitchandak's avatar
    amitchandak
    4 years ago

    Velvetine , You need to have common outlet and date table for that and join with both tabels and then try like

     

    outlet = distinct(union(distinct(Table1[outlet ]),distinct(Table2[outlet ])))

     

    CALCULATE(SUM(Table A[Sales]), FILTER(Outlet, Outlet[Outlet]<>"Online Store")) + CALCULATE(SUM(Table B[Sales]))

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Velvetine , You need to have common outlet and date table for that and join with both tabels and then try like

     

    outlet = distinct(union(distinct(Table1[outlet ]),distinct(Table2[outlet ])))

     

    CALCULATE(SUM(Table A[Sales]), FILTER(Outlet, Outlet[Outlet]<>"Online Store")) + CALCULATE(SUM(Table B[Sales]))