Create a graph with calculated values between 2 tables per week

[Syndicate_Admin]

Hello everyone

I want to generate a graph using values from two tables per week

Table 1						Table 2
Component 1	Week	Value				Component 2	Week	Value
A1	1	2.00				A2	1	8.00
A1	1	3.00				A2	1	5.00
A1	1	5.00				B2	1	4.00
B1	1	2.00				B2	1	4.00
B1	1	4.00				B2	1	3.00
A1	2	2.00				C2	1	4.00
A1	2	5.00				C2	1	2.00
B1	2	6.00				B2	2	6.00
C1	2	4.00				B2	2	5.00
C1	2	5.00				B2	2	4.00
A1	4	7.00				C2	2	1.00
B1	4	3.00				C2	2	2.00
B1	4	8.00				A1	3	8.00
B1	4	1.00				B2	3	7.00
B1	4	4.00				C2	3	4.00
C1	4	5.00				C2	3	3.00
C1	4	2.00				A2	4	5.00
C1	4	1.00				A2	4	6.00
						B2	4	4.00
						B2	4	4.00
						C2	4	3.00
						C2	4	1.00

For the graph, the values are given by subtracting the weekly average of each component subtracted from a constant.

Product	Component 1	Component 2	Fromula to Chart
A	A1	A2	A=(10)-(A1 - A2)
B	B1	B2	B=(11)-(B1 - B2)
C	C1	C2	C=(15)-(C1 - C2)

The problem is that there are weeks when you don't have a component, in that case you don't graph the value of that week

Week	Average A1	Average B1	Average C1		Average A2	Average B2	Average C2
1	3.33	3.67	N/A		6.50	3.67	3.00
2	3.50	6.00	4.50		N/A	5.00	1.50
3	N/A	N/A	N/A		8.00	7.00	3.50
4	7.00	4.00	2.67		5.50	4.00	2.00
	Calculation
Week	Product A	Product B	Product C
1	13.17	11.00
2		10.00	12.00
3
4	8.50	11.00	14.33

The graph would look something like this:

I hope you can help me

Best regards

[Anonymous]

Hi,@Syndicate_Admin 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.Create calculated table references:

Table = UNION('Table1','Table2')

3.Create calculated column references:

Average A1 = CALCULATE(AVERAGE('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Week]),'Table'[Component 1]="A1"))

Average A2 = CALCULATE(AVERAGE('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Week]),'Table'[Component 1]="A2"))

Average B1 = CALCULATE(AVERAGE('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Week]),'Table'[Component 1]="B1"))

Average B2 = CALCULATE(AVERAGE('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Week]),'Table'[Component 1]="B2"))

Average C1 = CALCULATE(AVERAGE('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Week]),'Table'[Component 1]="C1"))

Average C2 = CALCULATE(AVERAGE('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Week]),'Table'[Component 1]="C2"))

Product A = IF('Table'[Average A1]&&'Table'[Average A2],
10-'Table'[Average A1]+'Table'[Average A2],BLANK())

Product B = IF('Table'[Average B1]&&'Table'[Average B2],
11-'Table'[Average B1]+'Table'[Average B2],BLANK())

Product C = IF('Table'[Average C1]&&'Table'[Average C2],
15-'Table'[Average C1]+'Table'[Average C2],BLANK())

4.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[Billy_82476344 · Accepted solution]

Hello

Thank you very much for the answer, I had to adjust the graph a little so that it was identical to what I need, there was something that did not fit me but it was a mistake of mine in the table, but the answer is correct

Thank you and greetings!