Calculate a summary resale per product

[Anonymous]

Hi, I am struggling to calculate a summary per product in the following data structure:

Table1 - Customer DB

Customer Name	Address	No of Employees
Cust1	Address_Cust1	5
Cust2	Address_Cust2	10

Table2 - Sales YTD

Customer Name	Product name	QTY	Value
Cust1	A	5	10
Cust1	B	10	10
Cust1	C	1	2
Cust2	A	5	10
Cust2	B	10	10
Cust2	C	1	2

Table3 - Sales current month:

Customer Name	Product name	QTY	Value
Cust1	A	5	10
Cust2	B	10	10
Cust1	A	1	2
Cust1	C	5	15
Cust2	A	1	2
Cust1	C	10	30
Cust2	A	25	50
Cust2	B	10	10

Data structure:

I am using the following measure to calculate Total sales:

Total Sales = SUM(Sales_current_mnth[Value]) + SUM(Sales_YTD[Value])

If I try to calculate the Total sales per product the result is incorrect. If I try to add a relationship between Sales_YTD and Sales_current_mnth over Product Name, Power BI asks to deactivate existing relationship, which brings to incorrect calcutaion of Total Sales per Customer name. 

What can be done to overcome the issue? 

[FreemanZ · Accepted solution]

hi @Anonymous 

try to

1) add a product table;

2) connect with two sales tables with one to many relationships;

3) plot with the product column from the product table. 

[Anonymous · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _sum1=
SUMX(FILTER(ALL('Table3 - Sales current month'),'Table3 - Sales current month'[Product name]=MAX('Table2 - Sales YTD'[Product name])),[Value])
var _sum2=
SUMX(FILTER(ALL('Table2 - Sales YTD') ,'Table2 - Sales YTD'[Product name]=MAX('Table2 - Sales YTD'[Product name])),[Value])
return
_sum1 + _sum2

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[FreemanZ · Accepted solution]

hi @Anonymous 

try to

1) add a product table;

2) connect with two sales tables with one to many relationships;

3) plot with the product column from the product table.