Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
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:

GabrielLee_0-1607390815911.png

Table2:

GabrielLee_1-1607390870762.png

What I need:

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

YearProfitCost
20173610
20186710
20193610

 

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.

 

1 ACCEPTED SOLUTION
mparhi
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]
)
)

View solution in original post

4 REPLIES 4
mparhi
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]
)
)

Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

@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

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!