Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 belo...
Need Help
  • mparhi's avatar
    mparhi
    5 years ago

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

mparhi's avatar
mparhi
Frequent Visitor
5 years ago

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