Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
RolinMartis
Helper II
Helper II

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

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

 

BandHCHC%input optionHC to beHC% To be

exec

105%0

10

5.4%
Band 102010%02010.8%
band 93015%03016.2%
Band 88040%-206032.4%
Band 76030%56535.1%

 

 

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

 

Thanks in advance

 

1 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.

RicoZhou_0-1652340766522.png

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.

RicoZhou_3-1652341998556.png

 

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.

RicoZhou_1-1652341947021.png

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.

RicoZhou_2-1652341987484.png

 

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.

View solution in original post

6 REPLIES 6
mahenkj2
Impactful Individual
Impactful Individual

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.

 

mahenkj2_0-1652169705789.png

 

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

 

 

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.

Parameter'.PNG

 

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.

 

Parameter1.PNG

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.

RicoZhou_0-1652340766522.png

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.

RicoZhou_3-1652341998556.png

 

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.

RicoZhou_1-1652341947021.png

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.

RicoZhou_2-1652341987484.png

 

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.

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
Impactful Individual
Impactful Individual

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
Community Champion
Community Champion

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 😀

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.