Forum Discussion
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
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.
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
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]
)
)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
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
Hi,
You will not be able to solve this problem because there is no product column in Table2.
