Need help with building a simulator(what is model) in powerBI

[RolinMartis]

Hi Team,

I am new to powerBI. I need to build a simple simulator which will let you input the numbers and then shows the calculations based on the input.

template is below.

I have to show current HC and HC% by band and then let the user input the headcount actual numbers they want to change. Then can increase or decrease HC in specific band.

HC to be = HC+ Input Option

HC% to be = HC to be in a band / total HC to be

Band	HC	HC%	input option	HC to be	HC% To be
exec	10	5%	0	10	5.4%
Band 10	20	10%	0	20	10.8%
band 9	30	15%	0	30	16.2%
Band 8	80	40%	-20	60	32.4%
Band 7	60	30%	5	65	35.1%

Please suggest me how i can achieve this and if you have any usefull links please do share.

Thanks in advance

[mahenkj2]

Hello @RolinMartis ,

I would like to give a try this way:

I believe we can use Power BI for a simulator application, but perhaps with limitations depending how complex our requirement is.

I attach an example, using your sample information and can capture user input with the help of a drop down and calculate modified figures. It is close to what you may need, except that i created it as  a single user input for all the bands.

What I did:

Other than main table, I used Generateseries to create a series as a table to use as user input and then related to the main table with a dummy column (many to many). But since we are going to use slicer which can only select a single value, eventually it becomes 1 to many relation.

Then it is simple to calculate new measured using the user inputs.

This can be an example just to show that simulation is somewhat possible. I used this method in creating Histograms and using user selection for bin size variation.

Please find the sample file at link below:

https://drive.google.com/drive/folders/1NdUA2NU4enniMDEl5jipTIyZlKpGmlKl?usp=sharing

[RolinMartis]

HI @mahenkj2 

This is great. Thanks alot for your help.

My Requirement itself is to have multiple input boxes for each band. so that User can play around and change the numbers for each band and then see the HC% change.

Is there a way i can have the Parameter as "Type in" option other than list or dropdown.?

My users would want to type the numbers.

something like below inout button i want to give next to every band.

I created the parameter and added that as a filter. but its acting weird. Its reducing the number i am inputting.

if i type 20, it shows 19 when i hit enter. sameway 

Its infact reducing 1 every time i enter a even number. with odd numbers its totally fine.

[v-rzhou-msft · Accepted solution]

Hi @RolinMartis ,

Firstly, your issue that when you input 20 in slicer but it will show 19 is caused by too mant values. You can find this warning by clicking on "i" icon at the top of slicer.

The workaround is to add a filter to your data, you can reduce the range like let it show data from -100 to 100. Then slicer will work well.

If you want to input values as you want to each Band, I suggest you to create "What if parameters" as many as Bands you need.

Your origianl table should look like as below.

Then create measures to achieve your goal.

input option = 
SWITCH (
    SELECTEDVALUE ( 'Table'[Band] ),
    "exec", exec[exec Value],
    "Band 10", 'Band 10'[Band 10 Value],
    "band 9", 'Band 9'[Band 9 Value],
    "Band 7", 'Band 7'[Band 7 Value],
    "Band 8", 'Band 8'[Band 8 Value]
)

HC to be = 
CALCULATE ( SUM ( 'Table'[HC] ) )
    + SUMX ( VALUES ( 'Table'[Band] ), [input option] )

HC% To be = 
VAR _TOTAL =
    SUMX ( ALL ( 'Table'[Band] ), [HC to be] )
RETURN
    DIVIDE ( [HC to be], _TOTAL )

Result is as below.

Best Regards,
Rico Zhou

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

[RolinMartis]

Hi @v-rzhou-msft 

Thanks alot for the detailed explaination. IT was of great help.

But the problem is i want the parameters to let me enter any value without limit of min and max.

or atleast it should let me input between -10000 to +10000. 

By adding the the filter for parameter, i think i cannot input value higher than 100.

Any workaround for this ?

Regards,

Rolin

[mahenkj2]

For now, you need multiple band inputs to control inidvidual inputs. But I believe some of experts here can help you further achieve your final goal by using just one input table!

[speedramps]

Hi RolinMartis

Rower BI is read only.  It reads input files and outputs report.

You can create parmaters but paramaters apply to the whole file and not individual rows.

For example, you have a "fuel cost per mile" paramerts and use this on every row with a distance field to calculare total fuel expenditure.

You can't input different values for row Band 8 and 7 in Power BI, as per your example.

However, you can create a Power App and then include the Power App in the Power Bi report, and  that will do exactly what you want.

Please do the free online Power App training for more info

https://powerapps.microsoft.com/en-gb/

Please clcik thumbs up and accept as solution buttons. Thanks 😀