Forum Discussion

rsbin's avatar
rsbin
Icon for Community Champion rankCommunity Champion
6 years ago
Solved

Measure to Covert a Measure to a Constant Line

Good Evening, Have been struggling with this one for a while.  Reaching out to the Forum for additional help.   I have the following measure which is calculated based on User selection from a date...
Need Help
  • v-xicai's avatar
    v-xicai
    6 years ago

    Hi rsbin ,

     

    You may select on this chart visual, go to Analytics pane ->Average Line, put the [Benchmark] into Measure box, and set it more like below.

     

     

    Best Regards,

    Amy 

     

    Community Support Team _ Amy

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

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

When used at the day granularity in your visual, the Min and Max date in your measure are just that day, which is why you are getting different #s per day.  Please try this instead.

 

Benchmark =
CALCULATE (
    [EquipmentIDCount] / [TotalVisits],
    ALL ( GeneralStatistics ),
    ALL ( 'Calendar' )
)

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

  • rsbin's avatar
    rsbin
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi mahoneypat 

     

    Thanks for the reply.  Unfortunately, I think there is a slight misunderstanding.  The Benchmark calculation is correct.  Believe me, I spent a whole lot of time getting to that.  I need it to be date dependent.  That number changes whether I am looking at the first 90 days of my date range, the last 90 days or any timeframe inbetween.

     

    I am looking for a way to plot the result of that calculation on a time-series line chart so that it shows as a constant.

    Really appreciate the thought and suggestion.

     

    Best Regards,

     

    Best Regards and thanks again.

    • mahoneypat's avatar
      mahoneypat
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      I'm a little confused.  I was proposing that new measure to be plotted along with your original measure to show as a constant line.  Can you clarify what you are looking for by providing a few example rows of data and the expected outcome?

       

      Regards,

      Pat

       

      • rsbin's avatar
        rsbin
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Hello mahoneypat ,

        Thank you for your help to try to clarify.

        Here is some sample data. 

        DateEquipmentIDCountTotalVisitsBenchmark
        07/01/20198991950.216
        07/02/202574150660.171
        07/03/201881123220.153
        07/04/2077548990.158
        07/05/2060336720.164
        07/06/202822146500.193
        07/07/202702149250.181
        07/08/202778141620.196
        07/09/202726142510.191
         188501031420.183

        Using the following Measure, I get an answer of .183.  The reason for my ALL function is to remove any Facility filters on the page and then I keep the date filter.

         

        Benchmark = CALCULATE([EquipmentIDCount]/[TotalVisits], ALL(GeneralStatistics), DATESBETWEEN( 'Calendar'[Date],MIN('Calendar'[Date]), MAX('Calendar'[Date])))

         

          When I plot the above on a line chart I get the following blue line which is expected.

         

        My requirement is to be able to plot the .183 as a straight line constant.  I have simply hardcoded the Constant for the purposes of this image.

         

        Your suggested Measure, if I understand it correctly, calculates the Benchmark across my entire date range.  This gives a total of .181 with my current dataset.  I know it's close but that is just a coincidence with my selected date range.

         

        Benchmark =
CALCULATE (
    [EquipmentIDCount] / [TotalVisits],
    ALL ( GeneralStatistics ),
    ALL ( 'Calendar' )
)

         

         The closest I have been able to come is to use the Average Line from the Analytics Pane, but the Average does not give me the exact result...it is close.

         

        I hope this explanation  better explains what I am after and does not further confuse the issue.

        Again really appreciate your efforts on assisting me.

         

        Best Regards,