Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Subtract two columns based on conditions

Hi All,

 

I think this may be a simple question though I havent been able to figure out. What is the best way to set up a calculation to subtract two columns that share multiple conditions?

 

For example, in the view below I show Table 2 - Table 1 and the desired Results Table, where I am subtracting a combined revenue and cost for each Region and Date. The subtraction occurs based on the contents of Table 2 and Table 1 sharing the same Region and Date otherwise no calculation occurs.

 

What would the best approach be for this calculation?

 

 

Best,

JJ

Need Help
  • parry2k's avatar
    parry2k
    4 years ago

    Anonymous the best approach is to add a calendar and region dimension table which will have one to many relationships with both of these tables and then add measures for subtraction and for visualization, using date and region from the new dimension table.

     

    Profit = SUM ( Table2[Revenue] ) - SUM ( Table1[Cost] )

     

    Learn more about star schema here. Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

     

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

     

1 Reply

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

    Anonymous the best approach is to add a calendar and region dimension table which will have one to many relationships with both of these tables and then add measures for subtraction and for visualization, using date and region from the new dimension table.

     

    Profit = SUM ( Table2[Revenue] ) - SUM ( Table1[Cost] )

     

    Learn more about star schema here. Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

     

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.