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 slicer (365 days of daily data):

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

Once this has been calculated, I want to display the value as a Constant Line on a multi-line chart where the x-axis is a time series by date (i.e 0.17).


My issue is when I use this Measure as-is in my Visual, its value changes for every date along my x-axis.  I do not want that.  I have  tried to use the Average Line in the Analytics Pane.  Although, this gives me an approx. representation, it is not exact.  I am dealing with 3 decimal place accuracy. 

 

Would it be possible to write another Measure (call it ConstantBenchmark) or re-write this Measure to convert the value derived above into a constant value?

 

Any insights or suggestions would be much appreciated!

 

Kindest Regards,

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.

6 Replies

  • 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