Raw materials price tracking over the time

[ahadtk]

Dear All!

We have an Excel table like below:

	Price	Price	Price	Price	Price	Price	Price	Price	Price	Price	Price	Price	Price	Price	Price
	01-01-2022	02-01-2022	03-01-2022	04-01-2022	05-01-2022	06-01-2022	07-01-2022	08-01-2022	09-01-2022	10-01-2022	11-01-2022	12-01-2022	13-01-2022	14-01-2022	15-01-2022
Raw Material 1	5.64	4.22	2.25	9.8	8.18	4.54	5.86	7.2	2.63	6.65	1.06	4.49	2.22	5.05	7.54
Raw Material 2	3.59	6.82	7.61	2.06	6.62	4.35	3.04	2.78	7.63	6.31	1.51	6.05	7.27	2.97	3.61
Raw Material 3	5.95	4.02	5.93	1.33	5.13	1.17	6.4	1.04	9.94	7.87	3.91	1.82	4.15	9.51	5.1
Raw Material 4	4.9	1.58	5.23	1.04	4.89	3.26	9.76	4.71	7.74	5.1	4.36	1.2	2.33	3.54	9.26
Raw Material 5	8.65	1.55	8.71	7.39	5.85	8.32	6.69	8.52	5.77	8.43	8.86	3.2	8.43	3.15	3.87
Raw Material 6	8.71	8.2	3.16	4.79	8.02	2.47	1.88	3.41	9.65	2.53	3.22	7.6	3.2	2.2	4.56
Raw Material 7	6.89	2.2	4.86	7.1	6.19	6.84	9.91	5.29	6.86	3.27	2.18	8.67	4.3	1.05	2.64
Raw Material 8	3.72	8.78	8.35	1.2	6.43	8.42	4.11	6.5	1.35	8.04	3.41	1.56	2.32	8.38	5.03
Raw Material 9	5.07	4.01	6.34	8.7	2.36	5.83	7.63	3.97	5.11	4.98	5.71	1.39	9.31	6.57	3.72
Raw Material 10	7.04	4.26	9.55	4.64	4.16	8.13	5.09	4.3	2.72	8.13	9.66	6	3.35	7.35	4.53
Raw Material 11	5.98	5.52	8.2	1.51	3.23	4.85	1.51	7.28	8.74	3.51	6.96	9.5	7.3	2.98	3.37
Raw Material 12	3.45	8.71	6.49	3.73	3.29	1.42	1.23	8.36	2.92	7.79	1.99	1.23	1.03	2.44	8.8
Raw Material 13	2.76	7.22	6.93	9.89	7.06	9.16	5.8	1.49	2.96	5.49	5.47	8.36	7.17	1.15	5.21
Raw Material 14	8.68	5.38	7.18	8.14	6.11	9.85	1.52	1.5	1.98	7.34	7.91	3.85	2.67	4.04	2.9
Raw Material 15	9	8.89	4.4	4.39	3.12	8.04	3.26	9.54	8.35	3.83	5.69	1.64	2.7	5.95	4.96
Raw Material 16	6	6.29	2.25	1.75	6.69	9.08	8.87	1.23	8.77	9.73	8.31	4.8	7.85	1.52	6.49
Raw Material 17	1.73	5.02	9.78	4.67	9.81	3.38	5.74	7.35	8.53	4.12	3.26	3.32	9.23	5.24	6.69
Raw Material 18	8.05	3.2	7.24	7.61	4.96	9.41	9.95	4.35	5.66	7.83	5.13	3.11	1.86	3.28	8.46
Raw Material 19	9.43	2.12	1.88	9.71	6.77	7.58	3.97	3.45	8.36	2.58	2.81	2.49	6.51	3.54	1.54
Raw Material 20	9.01	3.13	5.46	7.02	1.56	6.05	3.77	5.63	5.02	1.2	1.54	4.6	7.01	5.65	4.43

And another excel table like below:

Product 1

=

Raw Material 18

+

Raw Material 19

+

Raw Material 15

Product 2

=

Raw Material 17

+

Raw Material 5

+

Raw Material 1

+

Raw Material 16

Product 3

=

Raw Material 8

+

Raw Material 2

+

Raw Material 16

+

Raw Material 17

+

Raw Material 19

Product 4

=

Raw Material 8

+

Raw Material 15

+

Raw Material 9

+

Raw Material 17

+

Raw Material 14

+

Raw Material 2

Product 5

=

Raw Material 2

+

Raw Material 14

+

Raw Material 8

Product 6

=

Raw Material 15

+

Raw Material 19

+

Raw Material 18

+

Raw Material 16

+

Raw Material 3

Product 7

=

Raw Material 16

+

Raw Material 11

+

Raw Material 9

+

Raw Material 5

Product 8

=

Raw Material 6

+

Raw Material 7

Product 9

=

Raw Material 11

+

Raw Material 13

+

Raw Material 7

+

Raw Material 14

Product 10

=

Raw Material 20

+

Raw Material 5

+

Raw Material 1

We need to display the corresponding raw materials prices over the time,

e.g.
If I select Product 4 (which has 6 raw materials) from a slicer on the left, it should show 6 separated line charts on the right regarding those 6 raw materials and show those prices over time,

Or, If I select product 10 (which includes 3 raw materials) from the slicer on the left, it should show 3 separated line charts, showing those prices over the time,

It would be great if you help me,

Many thanks,

[ahadtk]

Thanks a lot @v-rongtiep-msft 

[ahadtk]

Thanks @v-rongtiep-msft 

What about showing all corresponding line charts in one line chart,

Is that possible?

[v-rongtiep-msft]

Hi @ahadtk ,

Yes, you can get all the values in a line chart. You only need to select the product. Then if you want to see the signle materia after selecting the materia.

Best Regards

Community Support Team _ Polly

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

[ahadtk]

Hi @v-rongtiep-msft 

Many thanks for your help,

We almost there,

Indeed, we need multiple line charts at once,

For example, If we select Product 10 which includes 5 Raw Materials,

We need to see 5 line charts to see the Raw Material price changing over time,

Your file shows 1 Raw Material tracking at once and for tracking the rest, I need to select the rest Raw Materials manually,

We need to have all the rest line charts at once,

I don't know, is it possible or not?

Thanks,

[v-rongtiep-msft]

Hi @ahadtk ,

As far as I concerned,  there is no directly way to get the multiple line charts at once. You could use other visuals like tables. Or manually obtain the corresponding line charts.

Best Regards

Community Support Team _ Polly

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

[v-rongtiep-msft · Accepted solution]

Hi @ahadtk ,

The visuals's cannot be controlled by the slicer. But we can get the value in the table.

Please refer to my pbix file.

Create relationships between the tables.

If it still does not help, please provide more details.

Best Regards

Community Support Team _ Polly

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