Calculate Avg by year for a measure by Product

[Anonymous]

Hello Team, 

I have two tables as shown below - 

Table A: 

Month Start	Product Name	Sales Delta
1/1/2020	Prod A	15
1/1/2020	Prod B	28
2/1/2020	Prod A	16
2/1/2020	Prod B	29
3/1/2020	Prod A	17
3/1/2020	Prod B	30
4/1/2020	Prod A	18
4/1/2020	Prod B	31
5/1/2020	Prod A	19
5/1/2020	Prod B	32
6/1/2020	Prod A	20
6/1/2020	Prod B	33
7/1/2020	Prod A	21
7/1/2020	Prod B	34
8/1/2020	Prod A	22
8/1/2020	Prod B	35
9/1/2020	Prod A	23
9/1/2020	Prod B	36
10/1/2020	Prod A	24
10/1/2020	Prod B	37
11/1/2020	Prod A	25
11/1/2020	Prod B	38
12/1/2020	Prod A	26
12/1/2020	Prod B	39

Table B: 

Month Start	Product Name	Sales Delta
1/1/2020	Prod C	15
2/1/2020	Prod C	28
3/1/2020	Prod C	16
4/1/2020	Prod C	29
5/1/2020	Prod C	17
6/1/2020	Prod C	30
7/1/2020	Prod C	18
8/1/2020	Prod C	31
9/1/2020	Prod C	19
10/1/2020	Prod C	32
11/1/2020	Prod C	20
12/1/2020	Prod C	33

I have a relationship for these two tables with my Date table, which joins them based on Month Start as Key. 

After the join, if I create a table chart using fields from two tables - This is how it looks : 

Month Start	Product Name	Sales Delta	Product Name (From Table B)	Sales Delta (From Table B)	Diff on Sales
1/1/2020	Prod A	15	Prod C	15	0
1/1/2020	Prod B	28	Prod C	15	13
2/1/2020	Prod A	16	Prod C	28	-12
2/1/2020	Prod B	29	Prod C	28	1
3/1/2020	Prod A	17	Prod C	16	1
3/1/2020	Prod B	30	Prod C	16	14
4/1/2020	Prod A	18	Prod C	29	-11
4/1/2020	Prod B	31	Prod C	29	2
5/1/2020	Prod A	19	Prod C	17	2
5/1/2020	Prod B	32	Prod C	17	15
6/1/2020	Prod A	20	Prod C	30	-10
6/1/2020	Prod B	33	Prod C	30	3
7/1/2020	Prod A	21	Prod C	18	3
7/1/2020	Prod B	34	Prod C	18	16
8/1/2020	Prod A	22	Prod C	31	-9
8/1/2020	Prod B	35	Prod C	31	4
9/1/2020	Prod A	23	Prod C	19	4
9/1/2020	Prod B	36	Prod C	19	17
10/1/2020	Prod A	24	Prod C	32	-8
10/1/2020	Prod B	37	Prod C	32	5
11/1/2020	Prod A	25	Prod C	20	5
11/1/2020	Prod B	38	Prod C	20	18
12/1/2020	Prod A	26	Prod C	33	-7
12/1/2020	Prod B	39	Prod C	33	6

The sales delta on either of the tables are measures which calculate the delta value in sales. Now, I want to get the avg of the "Diff of sales" measure by Year. 

 I am planning to include Product Name from table A as a slicer on the visual. So when there is no selection then the measure "Sales Delta" (Which is shown as column in table above) sums values for Prod A and Prod B and compares with Prod C. 

This is how the data looks in that case - 

Month Start	Sales Delta from Table A	Sales Delta from Table B	Diff on Sales
1/1/2020	43	15	28
2/1/2020	45	28	17
3/1/2020	47	16	31
4/1/2020	49	29	20
5/1/2020	51	17	34
6/1/2020	53	30	23
7/1/2020	55	18	37
8/1/2020	57	31	26
9/1/2020	59	19	40
10/1/2020	61	32	29
11/1/2020	63	20	43
12/1/2020	65	33	32

If you see, the sales delta from table A is the sum of both product A and Prod B. Now, I am using the below formula to calculate the average for the year - 

Delta Avg by Year = calculate(AverageX(SUMMARIZE(TABLE A,TABLEA[MonthStart], "Avg", [Diff in Sales]),[Avg]),

filter(ALLSELECTED(TABLEA),TABLEA[Year]=max(TABLEA[Year])))

However, the above formula is not working when the user filters by product name on Table A. The average calculated should be based on values for Prod A but instead it is averaging for both products included. 

Please let me know where I am wrong on this one. Thank you. 

[nandic]

Hi @Anonymous ,
If table A and table B have exactly the same structure, i would go with union.
Firstly create union in Power Query for tables A and B. Then set only one relationship between this new table and Date table.
Afterwards create 3 measures:

AB Amount = CALCULATE(SUM(Append1[Sales Delta ]),FILTER(Append1,Append1[Product Name] in {"Prod A","Prod B"})) 

C Amount = CALCULATE(SUM(Append1[Sales Delta ]),FILTER(Append1,Append1[Product Name] = "Prod C"))

Difference = [AB Amount] - [C Amount]

This is the result, attached also pbix file.

Regards,

Nemanja Andic

[Anonymous]

Thank you, Nandic. One of my needs is to have a slicer available on the report for the user to pick either Prod A or Prod B to compare diff with Prod C. When we do union, then that would not be possible right ? Cause, when the user filters for prod A then we dont have measure based on prod C calculated properly as the data gets filtered based on slicer. Please bear with me as I am new to Power BI. Thanks for your help. 

[nandic]

Hi, attached new version.
Union table is still used, but for filtering i created disconnected table "Product". It has no relationship to date or Append table.
There are 2 measures in the file:
1) AorB = if user can select only one product and it is mandatory

AorB Amount =

var _selectedProduct = SELECTEDVALUE('Product'[Product],"Prod A") -- if filter is radio button and always one value will be selected, then no need to specify this alternate result ("Prod A")

RETURN

CALCULATE(SUM(Append1[Sales Delta ]),FILTER(Append1,Append1[Product Name] = _selectedProduct))

2) AandB = if user can select both Prod A and Prod B and to compare total (A+B) to Prod C

AandB Amount =

var _selectedProducts = values('Product'[Product])

RETURN

CALCULATE(SUM(Append1[Sales Delta ]),FILTER(Append1,Append1[Product Name] in _selectedProducts))

Regards,
Nemanja Andic