Forum Discussion

android1's avatar
android1
Icon for Post Patron rankPost Patron
10 years ago
Solved

Dropdown list to change value in a formula

Hi,   I have a formula which shows the % of people who worked 85% or more of their scheduled time. I would like the Dashboard viewer to be able to change the 85% to a number of their choosing. Is...
Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    10 years ago

    android1

     

    Thanks for uploading.

    Not a calculated column in this case, just replace the [in full] measure from

    In Full = 
COUNTROWS ( FILTER ( OTIF, OTIF[InFull] = "In Full" ) )

    to

    In Full = 
COUNTROWS ( FILTER ( OTIF, [ActualMinutes]>=[ScheduledMinutes]*[targetPerc]) )

    Please compare the [In Full]&[In Full%] and [In Full2]&[In Full 2 %] in the attachment. Download it by clicking here.

     

    If it answers you question, please accept it as solution. For more questions, feel free to let me know.

     

android1's avatar
android1
Icon for Post Patron rankPost Patron
10 years ago

Hi,

 

I used a calculated column because I could not reference the columns I needed in a measure.

Here's a screenshot of my data & formula -> 

Eric_Zhang's avatar
Eric_Zhang
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

android1

The snapshots are blurred and it doesn't allow to enlarge to see details. So I can't get your point.

Would you mind uploading the pbix to a net storage and share? If it is ok, do remember to mask sensitive data before uploading.

    • Eric_Zhang's avatar
      Eric_Zhang
      Icon for Microsoft Employee rankMicrosoft Employee
      10 years ago

      android1

       

      Thanks for uploading.

      Not a calculated column in this case, just replace the [in full] measure from

      In Full = 
COUNTROWS ( FILTER ( OTIF, OTIF[InFull] = "In Full" ) )

      to

      In Full = 
COUNTROWS ( FILTER ( OTIF, [ActualMinutes]>=[ScheduledMinutes]*[targetPerc]) )

      Please compare the [In Full]&[In Full%] and [In Full2]&[In Full 2 %] in the attachment. Download it by clicking here.

       

      If it answers you question, please accept it as solution. For more questions, feel free to let me know.

       

      • android1's avatar
        android1
        Icon for Post Patron rankPost Patron
        10 years ago

        Hi,

         

        Thanks a million, that works perfectly.

         

        I also have a calculated column Punctuality = IF (OTIF[ShiftInTime]<OTIF[TimeFrom]-1/24*.25,"Early",IF (OTIF[ShiftInTime]>OTIF[TimeFrom]+1/24*.25,"Late","On Time" & IF (OTIF[ShiftInTime]>OTIF[TimeFrom]+1/24*1,"Perfect")))

         

        This states the appointment is On Time if they clock in 15 mins before OTIF[TimeFrom] or 15 mins after OTIF[TimeFrom] ie if your appointment is at 4, you are on time anywhere between 3:45 to 4:15. You are early if it's any time before 3;45 and late if it's any time after 4:15.

         

        I am looking to do the same thing as before. I have added a table OT % with values for 5, 10 mins etc

        I have also added this measure -> targetperc OT = LASTNONBLANK('OT %'[OT %],0.25)

         

        I am struggling adapting this measure -> On T(+15-15) =
        COUNTROWS ( FILTER ( OTIF, OTIF[Punctuality] = "On Time" ) ) to utilize the targetperc OT measure