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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

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!