Measure to Covert a Measure to a Constant Line

[rsbin]

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,

[mahoneypat]

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]

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]

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]

Hello @mahoneypat ,

Thank you for your help to try to clarify.

Here is some sample data. 

Date	EquipmentIDCount	TotalVisits	Benchmark
07/01/20	1989	9195	0.216
07/02/20	2574	15066	0.171
07/03/20	1881	12322	0.153
07/04/20	775	4899	0.158
07/05/20	603	3672	0.164
07/06/20	2822	14650	0.193
07/07/20	2702	14925	0.181
07/08/20	2778	14162	0.196
07/09/20	2726	14251	0.191
	18850	103142	0.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 · Accepted solution]

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]

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,