Calculating a table with variables

[Aidarbek]

Hi everyone!
Could you please help with following question.

I have a data of the projects with their projected Revenue and expenses.

Revenue is divided by hard (100% we will get this payment) and soft (questionable) parts.

Also, there is a 4 scenario for revenue, like 1st - we won't have a soft part of Revenue, 2nd - 50%, 3rd - 100%, 4th - 120%.

Same situation with an expenses. Some parts will be as it planned, but some parts will be affected by indextation.

Percentage of hard and soft parts also is questionable (it could be 50%-50%, or 60-40%, or 70%-30%)

Again, there is a few situation, like indexation for 5%, 10% and 15%.

And all this scenarios are not intercept each other.

The Idea is a create a table where We could see how the amount and the quantity of projects with loss.

To take a Percentage as a slicer, column as a scenarion of Revenue and Lines as a scenario of Costs.

My working table look like a following one:

Projects	Revenue	Cost
A	976	850
B	926	819
C	936	808
D	954	766

I tried to create a table, but here I've been told that it is not possible to create a table wich relies on variable.

I have made a measure to calculate the Profit, but I need help with counting a Projects with negative profit.

Could you please give me any Ideas how I could do it?

[danextian]

Hi @Aidarbek 

Technically speaking you can use varibles in a calculated table but these variables will not respond to slicer selections and so is your calculated table as it is updates only upon creation or the underlying/referenced data has been changed such as upon refresh. That aside, can you please post your expected result and not just describe it.? Given your current sample data, what information in a table do you expect if from those different scenarios? A link to an excel file with all the formula/calculations will be very helpful.

[FarhanJeelani · Accepted solution]

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.

[Aidarbek]

Thank you!

I used Distinctcount which didn't work, but your Idea to use countrows worked perfrectly.

Thank you!