Forum Discussion

ggfd's avatar
ggfd
New Member
2 years ago
Solved

Adding a new column from another with specific cell values

Hello group, I have a spreadsheet column with incorrect values. However, there's no "one size fits all". In other words, I cannot apply a reduction of 25% to the entire column, as some cells are cor...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi ggfd ,

    You can achieve this in DAX easily if your data can be transformed into this:

    The data types of all columns except the first are Decimal number.
    You can use these DAXs to create two new columns:

    Fixed Expected Revenue 2023 = 'Table'[Expected Revenue 2023 (PowerBI column Type: decimal number)] * (1 - 'Table'[TASD1200])
    Fixed Expected Revenue 2024 = 'Table'[Expected Revenue 2024 (PowerBI column Type: decimal number)] * (1 - 'Table'[TASD1200])

    The final output is as below:

     

    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ggfd's avatar
ggfd
New Member
2 years ago
 

 

2 seconds ago

Hello,
I'm sorry for the delay. I'm new on PowerBI so I was reviewing the problem to better describe what I'm trying to do.

Part of my dataset is below. The columns in red don't exist, so they are what I've been trying to create using python:

Close Reason (PowerBI column type: text)Expected Revenue 2023 (PowerBI column Type: decimal number)Fixed Expected Revenue 2023 (PowerBI column Type: decimal number)
Expected Revenue 2024 (PowerBI column Type: decimal number)Fixed Expected Revenue 2024 (PowerBI column Type: decimal number)
no feedback from end customer. [TASD1200=25%]USD 200.00 USD 1,000.00 
unsuccessful testing [TASD1200=32%]USD 500.00 USD 250.00 
Project cancelled. [TASD1200=100%]USD 800.00 USD 350.00 
Opportunity created in the wrong currency USD 750.00 USD 200.00 
won, time to marketUSD 281.58 USD 100.00 

 

The new columns, "Fixed Expected Revenue 2023" and "Fixed Expected Revenue 2024," should be filled with the values obtained by subtracting the discount percentage specified in "Close Reason" column TASD1200 percentage from the "Expected Revenue 2023" and "Expected Revenue 2024" respectively.

Example:

Close Reason (PowerBI column type: text)Expected Revenue 2023 (PowerBI column Type: decimal number)Fixed Expected Revenue 2023 (PowerBI column Type: decimal number)
Expected Revenue 2024 (PowerBI column Type: decimal number)Fixed Expected Revenue 2024 (PowerBI column Type: decimal number)
no feedback from end customer. [TASD1200=25%]USD 200.00200 - (200*25%) = 150USD 1,000.001000 - (1000*25%) = 750

 

Is it possible to create that using python?

E.g.

Transform data > Run Python script?

 

I prefer to use Python rather than DAX. However, if Python is too complicated, a DAX script is fine.

 

Thanks a lot for the help,

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi ggfd ,

    You can achieve this in DAX easily if your data can be transformed into this:

    The data types of all columns except the first are Decimal number.
    You can use these DAXs to create two new columns:

    Fixed Expected Revenue 2023 = 'Table'[Expected Revenue 2023 (PowerBI column Type: decimal number)] * (1 - 'Table'[TASD1200])
    Fixed Expected Revenue 2024 = 'Table'[Expected Revenue 2024 (PowerBI column Type: decimal number)] * (1 - 'Table'[TASD1200])

    The final output is as below:

     

    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.