Forum Discussion

aucklanduser's avatar
aucklanduser
Helper I
7 years ago

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 insta...
Need Help
aucklanduser's avatar
aucklanduser
Helper I
7 years ago

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's avatar
    aucklanduser
    Helper I
    7 years ago

    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?

     

     

    Thanks

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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?

       

       

       


      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]))



      Regards,
      Lydia

      • aucklanduser's avatar
        aucklanduser
        Helper I
        7 years ago

        Hi Lydia,

         

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

         

        Thanks