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.

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,

     

     

     

    • v-xicai's avatar
      v-xicai
      Icon for Community Support rankCommunity Support
      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.

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

        Hello v-xicai 

        Thank you for this suggestion.  As noted earlier in the thread, this is the best solution that I have come up with thus far.

        I have tweaked it slightly to use a Card to show the correct data value, as the Average value is not as accurate.

        Appreciate you taking the time to respond.

         

        Best Regards,