cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rsbin
Post Partisan
Post Partisan

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,

1 ACCEPTED SOLUTION
v-xicai
Community Support
Community Support

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.

130.png

 

 

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.

View solution in original post

6 REPLIES 6
mahoneypat
Super User
Super User

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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.

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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.

 

image.png

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
Community Support
Community Support

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.

130.png

 

 

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.

View solution in original post

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,

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!