Using the “Whatif” feature in Power BI to build a Return on Investment (ROI) Calculator – John Broomfield

      Comments Off on Using the “Whatif” feature in Power BI to build a Return on Investment (ROI) Calculator – John Broomfield
Using the Whatif feature in Power BI to build a


Not long ago I was tasked with making a Return on Expenditure Calculator in Electricity BI, for our profits team to reveal what type of return a client may possibly acquire for their marketing commit.

So I needed to give range inputs to deliver this and rapidly located there are no out of the box or personalized visuals to produce this.
W.T.F?!  No, hold out, I can use What-if parameters!

In this scenario we employed the adhering to inputs…

  • Date Time period – a date assortment. by default we use the last 12 months, as it simplifies the method to figure out and enter the other values.
  • Promoting Financial investment $  – what they invest with us for each 12 months. NB: We could get this from our database. we will do this in period 2 of the software.
  • Ordinary Buyer Devote $ – what an typical consumer spends for each calendar year. This should really be delivered by the shopper

We then use targeted visitors knowledge (profile clicks) as a foundation to decide guide to purchaser conversion.

PBI Whatif 2018 08 21 1656

There are then inputs for conversion from sales opportunities to prospects, which the client really should also deliver dependent on their knowledge.

There are some restrictions on how you can use the “what-if” characteristic and I will run by incorporating a what-if parameter to spotlight this. This function was manufactured active in the August 2018 launch and is briefly explained listed here

To produce a new What If parameter, find “new Parameter – What If” from the modelling menu in Ability BI.

Whatif create 2018 08 22 1624

You need to give your parameter the next:

  • Title: This is what it is named in the Design. you can modify the label if you use as a filter
  • Details Sort: full, decimal or fixed decimal. I utilized decimals for the percentages in click on to client conversion costs.
  • Least: commencing benefit for array
  • Optimum: ending price for variety
  • Increment: value the vary increment by
  • Default: what price really should be applied on load

This then generates a desk in your design with the incremental values and the default value as a evaluate. this is what mine appear like.

  • Advertising Investment decision = GENERATESERIES(, 10000, 100)
  • Campaign Investment decision Price = SELECTEDVALUE(‘Advertising Investment'[Campaign Investment])

As you can see from the Advertising and marketing Investment we now have a sequence that goes from 100 to 10000. This is the place I emphasize the limitation of working with what if – you can only use a utmost of 1000 values.

This offers an concern for us specially in moving into regular buyer commit, as thanks to the varient of our clients it could range from $10 (cafe) to $50,000 (pool builder). This has been observed as a shorter coming and you can vote for Microsoft to resolve it by likely listed here

Ignoring this, the what if parameter has provided a very useful way of supplying numeric input in your Power BI reviews. An additional major thumbs up to the Electric power BI crew.

thumb 762055


Supply link