Dynamic inputs

[jacobsutcliffe]

I am building a report in Power BI which shows the details of sales opportunities being pulled from our CRM which are forecast to close that month.

Currently we have a weekly team meeting where we go through each opportunity in an Excel spreadsheet with the sales reps and assign each opportunity with one of three categories: Will make the sale, Might make the sale and Won't make the sale. This allows us to forecast our revenues for the month.

We are trying to transition to Power BI, is there a way to assign a value to each opportunity in Power BI using a custom visual or power app so we can run calculations based on these inputs? 

[v-yangliu-msft · Accepted solution]

Hi  @jacobsutcliffe  ,
You can use the switch() function to label them as required

According to your description, I create this data:

Custom rules:

Every week, Good and Bad of the same Opportunity are added together,

If it is greater than 0, it means Will make the sale

If it is equal to 0, it is May make the sale

If it is less than 0, it is Not make the sale

Here are the steps you can follow：

1. Create calculated column.

Week = WEEKNUM('Table'[date])

Categories =
var _flag=
SUMX(FILTER('Table','Table'[Week]=EARLIER('Table'[Week])&&'Table'[Opportunity]=EARLIER('Table'[Opportunity])),'Table'[Good]+'Table'[Bad])
return
SWITCH(
    TRUE(),
    _flag>0,"Will make the sale",
    _flag=0,"May make the sale",
    _flag<0,"Not make the sale")

2. Result:

You can downloaded PBIX file from here.

If the result is not correct as you expected, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

what are the rules defined by the three categories.

Best Regards,

Liu Yang

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