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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aucklanduser
Helper I
Helper I

User input in desktop

Im trying to design a better process for our cost accountant, he is currently spending hours each month waiting for bulky spreadsheets to calculate, our 20 million odd line dataset loads almost instantly in powerbi. What he needs however is the ability to exclude specific job assemblies from a calculation. As this is "art not science", it cannot be calculated, reasons need to be taken into account before a decision is made whether to include or exclude outliers. Preferably dont want to filter the operations individually as there will be many thousands of operations a month to look through. Ideal output would be an "include" column that has jobs over 200% of standard or under 50% of standard, which can be set by the user as 1 or 0, then filter on this. 

 

Sorry if this is very vague, more about the concept of having a user editable element, just trying to cut down some excel processing time.

12 REPLIES 12
Anonymous
Not applicable

Hi @aucklanduser , were you able to find a solution? I am looking to solve a very similar problem.

@Anonymous , yes and no, we haven't implemented a solution however I think its possible by embedding a PowerApp. You will however need an Enterprise license for PowerBI if you want the data to refresh after you have made changes (annoyingly, the only real use for it) or you have to wait for your scheduled refresh

parry2k
Super User
Super User

@aucklanduser if you can provide some sample data and business rule around what to exclude/include , it wil help to provide the solution. Even What-If parameter can be use in this scenario if % are not fixed for include/exclude.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hey Parry,

 

Havent developed the dataset yet as I will just leave it if its not possible but it will look like;

 

Date   Job#   Assy#   ActualHours   StandardHours   VarToStd   Var%   Include/Exclude?

 

For each jobs assembly, we are assessing the variance to our current standard (e.g the build operation for widget A normally takes 1 hour but for job 1 it took 3 hours). He currently pulls anything less than 50% of the current standard or over 200% of the current standard (100% positive / minus) and then looks at the reasons for the variances and will either include or exclude these in the calculation of the new standard. The user needs the ability to flag particular rows as either include or exclude in the new average hours (standard) calculation for each assembly.

 

How would a what if parameter be used? Many thanks for your help

@aucklanduser,

Please share sample data of your table and post expected result based on sample data following the guide in the blog below.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thansk Lydia, the data will be laid out like the table below, however the Include/Exclude table needs to be manually adjusted. The check is calculated on whether or not Var% is less than 50% or greater than 200%. We sometimes want to include and sometimes exclude these variances as they may just be system errors which we do not want to include. Based on the input from the include or exclude, the NewStd column will pull either the ActualHours (if yes) or StandardHours (if no). We will then average the NewStd column by job to get our new standard hours.

 

Main question being, is it possible in powerbi to have a parameter or other form of user input that can set a variable for a row?

 

Capture.PNG

 

Thanks

@aucklanduser,


Main question being, is it possible in powerbi to have a parameter or other form of user input that can set a variable for a row?

 

Capture.PNG

 

 



If you mean create Var% column dynamically based on user input, you can create what if paramater in Power BI Desktop, then create a measure named Var%.

Var% = Parameter[Parameter Value]*MAX(Table1[Var])


Then you can crate the following measures.

Include/Exclude = IF([Var%]>2, "No", IF([Var%]<0.5, "Yes",BLANK()))
NewStd = IF([Include/Exclude]="Yes" ||ISBLANK([Include/Exclude]),MAX(Table1[ActualHours]),MAX(Table1[StandardHours]))

Capture.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Lydia,

 

The user needs to decide whether or not to include or exclude, it is not based on a formula,

 

Thanks

@aucklanduser,

Please elaborate that what variable the users would input, and what result would you like to get based the input based on above sample data.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Lydia,

 

The user will input either include or exclude (1 or 0) to include or exclude the row from the calculation based on the cost accountants judgement

@aucklanduser,

If the Include/Exclude column is a existing field or a calculated column in your table, you can create a slicer using this field for user to choose.

Otherwise, you can create what if parameter(1 or 0) as my previous reply. If you still have questions, please post expected result based on  the above sample table, and elaborate more about your desired result following the guide in the blog below.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Lydia,

 

Think we are going round in circles. The include/exclude column is not an existing field its a theoretical user entered field. The end user needs to determine whether or not each row will be included. Not based on formula. This is a manual input by the end user. No formula can decide whether or not each row is included or excluded. I dont think this is possible, I was just asking if anyone knows of a workaround as excel processing times are rolling into the hours. 

 

No expected result can be generated as it will depend on the users judgement of each row and whether or not said row should be included in the new calculation or excluded.

 

Many thanks,

 

Paul

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.