Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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]
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]
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |