Need to replace cell value with dynamic value

[Anonymous]

Hi, I need help to create a dynamic table

Lets say I have 2 table as below 

Table1:

Table2:

What I need:

1. Calculate sum of 2017, 2018, 2019 profit and sum of cost in table2 as below

Year	Profit	Cost
2017	36	10
2018	67	10
2019	36	10

2. I need the sum of profit and cost to be dynamic as i filter the product

 example: Top 3 product based on profit

here is the link of the report

https://drive.google.com/file/d/1vLQ-R4iOiYZC8rbGat_kM4zKRhQ68Phi/view?usp=sharing

I'm new to Power BI so any help will be much appreciate.

[mparhi · Accepted solution]

Use the DAX function to create an unpivot table. Create one for Profit and a separate table for Cost and then create a relationship using the Product column,

TB_ProfitByYear =

UNION (
SELECTCOLUMNS (
Sheet1,
"Product", Sheet1[Product],
"Year", 2017,
"Profit", Sheet1[2017 Profit]
),
SELECTCOLUMNS (
Sheet1,
"Product", Sheet1[Product],
"Year", 2018,
"Profit", Sheet1[2018 Profit]
),
SELECTCOLUMNS (
Sheet1,
"Product", Sheet1[Product],
"Year", 2019,
"Profit", Sheet1[2019 Profit]
)
)

[Ashish_Mathur]

Hi,

You will not be able to solve this problem because there is no product column in Table2.

[amitchandak]

@Anonymous , In the first table, you can unpivot the three profit year column and then split them.

https://radacad.com/pivot-and-unpivot-with-power-bi
https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

Then create a common year column and join to both tables

[Anonymous]

thanks fo reply

I'm not sure i understand what you mean. Could you explain more to me ?

here is the link of the report

https://drive.google.com/file/d/1vLQ-R4iOiYZC8rbGat_kM4zKRhQ68Phi/view?usp=sharing

I would really appreciate if you can show me how to do that