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

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,