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

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

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

 

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.