Calculating a table with variables

Hi Aidarbek ,

It seems like you're working on a complex scenario analysis in Power BI where you want to calculate the number of projects with negative profit under various scenarios. Here's how you can approach this problem effectively:

Create Measures for Revenue and Costs:

• Define measures to calculate revenue and cost based on your scenarios and percentages. Use DAX to incorporate dynamic calculations.
• Example for Revenue:

  Revenue_Scenario = 
  SWITCH(
      SELECTEDVALUE(RevenueScenario[Scenario]),
      "1", SUM(Projects[HardRevenue]),
      "2", SUM(Projects[HardRevenue]) + 0.5 * SUM(Projects[SoftRevenue]),
      "3", SUM(Projects[HardRevenue]) + SUM(Projects[SoftRevenue]),
      "4", SUM(Projects[HardRevenue]) + 1.2 * SUM(Projects[SoftRevenue])
  )

• Similar logic can be applied for cost, using indexation scenarios.

  Calculate Profit Measure: Use the revenue and cost measures to calculate profit:

  Profit = [Revenue_Scenario] - [Cost_Scenario]

  Count Projects with Negative Profit: Create another measure to count projects where profit is negative:

  Negative_Profit_Projects = 
  COUNTROWS(
      FILTER(
          Projects,
          [Profit] < 0
      )
  )

  Create Scenario Tables:

  • Add slicers for Revenue Scenarios and Cost Scenarios. These can be created as separate disconnected tables with a list of scenarios.

Example for Revenue Scenarios Table:

Scenario
1
2
3
4

Set Up the Matrix:

• Place the Revenue Scenarios in columns.
• Place Cost Scenarios in rows.
• Add the measure Negative_Profit_Projects as the value.

Enable Interactivity with Slicers:

• Use slicers for the percentage split of hard/soft revenue and the indexation percentages for costs.
• These slicers should dynamically update the calculations in the table.

Please mark this as solution if it helps you. Appreciate Kudos.