cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## Need to replace cell value with dynamic value

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

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

1 ACCEPTED SOLUTION
Frequent Visitor

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]
)
)

4 REPLIES 4
Frequent Visitor

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]
)
)

Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

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

Then create a common year column and join to both tables

Anonymous
Not applicable

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

here is the link of the report

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors