Need to create a measure based on values from 2 tables and grouping

[Anonymous]

Power BI Experts, Please help as I need to create a column chart based on the amount of product sold minus amount of products returned. Suppose I have 2 tables Sales and Return as mentioned below. Now I need to  draw a column chart showing net sales (Sales-return) of products. Product name on x axis and sum of amount(sales - return) on y axis.

Sales Table

Product  Amount

A               5

B               4

A               3

C               4

B               3

B               4

C               5

Return Table

Product        Amount

A                     3

A                     1

B                      2

B                      1

C                     1

[Anonymous · Accepted solution]

This can be solved with basic data modeling.  You need a common dimension table for Products.  This should be a DISTINCT list of every Product Code (A, B, C, etc.)

Then you create a  One-to-Many relationship betwheen the Products table and both the Sales and Returns tables.

Use the Product table's Product Code column in the chart

then make 3 measures

[# Sales] = SUM( Sales[Amount] )

[# Returns] = SUM( Returns[Amount] )

[# Net Sales] = [# Sales] - [# Returns]

[nkasdali]

Better to use variables :

MyMeasure = 
var vAmount = SUM( Sales[Amount] )
var vReturn = SUM( Returns[Amount] )
return
[# Net Sales] = vAmount  - vReturn 

in this case, you'll see only the finale measure into your dataset

[Anonymous]

thanks Chris. It works.