Formula "recommending" the best option based on the criteria

[Jolcyna]

Dear Experts,

I hope you can help / share your perspective on the following problem I am trying to crack in PBI.

I am thinking about “building” a formula that would help would suggest the solution depending on the criteria. Sth like “Solver” in excel but simpler.

Let’s assume we have a target revenue (47,000) we need to achieve each year. In order to do that we need to make sure that there’s no gap in revenues (11,000). It means we need to sign contract with new clients. We have a list of potential clients with potential revenues and the potential length of contract for each (‘Client Table).

Problem: We need to fill in “revenues gap” signing the smaller  (i.e. max 4 clients) number of new contracts to fill in gap to the closes possible value – but +/- 10% the gap. Of course, the number of combinations in a real life huge.  The best client combination is also defined by the length of the potential contract - the longer the better.

So I need the formula that would go row by row, first summarize the revenues of the companies with the longest potential contract length to the moment until it achieves the target (i.e. those with 10y long contracts) and see if it’s possible with signing contract with 4 clients only. If not – switch to those with shorter contract.

Something like this. Do you think it’s feasible? I tried – but failed. Attached the sample data and desired outcome (“Recommended client” = Yes)

THANK YOU SO MUCH FOR READING IT!:-)

Targets:

Annual revenues	£36,000
Target	£47,000
Gap target	£11,000 (+/-10%)

Compny name	Potentil Revenues	Potential contract lenght	Recommended client
Com1	£15,600	5
Com2	£1,234	5
Com3	£123	10	Yes
Com4	£7,854	10	Yes
Com5	£347	5
Com6	£215	5
Com7	£746	5
Com8	£3,214	10	Yes

Sum of recommended	£11,191
Within acceptable range	Yes

[v-stephen-msft · Accepted solution]

Hi @Jolcyna ,

Here's the possible idea.

Create a what-if parameter to get the range of target.

Create a measure to get the ranking values. Based on this ranking, you can find the maximum revenue for each group by group and compare it to the target range.

Best Regards,

Stephen Tao

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