Dynamic constant line

[miltenburger]

Hi Guys,

I'm trying to figure out if there is a possibility to work with a dynamic constant line. My current visual is based on healthcare data. What I can show them right now is the running total based on a year. The constant line shows me the budget based on a hardcoded budget. See picture below:

What I want, when I choose for another year in the filter, my budget (constant line) changes automatically. Is there a possibility to achieve this?

Thanks in advance

[marcoselias]

Another approach/trick that worked for me was:

1. Create a measure (may work with a calculated column that will later be summarized as average, MAX, MIN, etc.). The measure (or aggregation of calculated column) will have the dynamic value of the constant line you want to display. In my case, I used a measure and I am showing a constant line that changes value depending on the current month.
2. Create your visual without the constant line first. In my case, I am using the clustered bar chart.
3. Add the measure with the constant value to your list of values of the visual. The value will appear in your visual. In my case, it appeared as another horizontal bar. You'll hide it in a later step.
4. Go to the analytics tab of the visual and instead of adding a new Constant Line, add a Min Line, Max Line, or whatever line makes more sense for your measure, column, visual, etc. as long as the option you choose allows you to select the measure/column you added in the previous step. From the formatting options of the line you added, click on the dropdown labeled "Measure" and pick the measure that has the value of the constant line.
5. Finish formatting the line as you want (title, text position, color, etc.).
6. Go back to the Fields tab of the visual and drag the measure with the constant value from the Value group to the Tooltips group. The value will now disappear from the visual but the constant line should remain.
7. (optional) If you do not want the value of the now dynamic constant line to show in the Tooltip of the visual, create a custom visual. However, keep in mind that if the user is viewing the report in a way that doesn't support report page tooltips, the user will see the default Tooltip including the value of the dynamic constant line you just created.

[Anonymous]

Hello @marcoselias I tried the steps as following. However the constant line has no value showing. The target should be 5.2bn. Any suggestions. thank you

[eeramirezo]

I found,  that is  possible to achieve that.  Using  this  steps: 

1.  Create a  Measure that have the desired  values,  according with  the value  of slicer.  It could be in  a form  of  if  statement.  If  selected value  is  x  then  y. 

2.   Add constant  line  in  Analytics  section.  In the value,  you can select  the  fx   logo  an  then  you  can  base the value  of this  constant  line based on your measure.  Now  based  on  selection,  your constant  line  (or threshold)  will change.

[marcoselias]

Is the measure that you are going to use as a constant line evaluating to the 5.2bn that you are expecting? you can verify this with a card visual and dragging the measure to the Fields well. If it is evaluating correctly, did you drag the measure as a value of the visual (step 3 above) before going to the analytics tab to add a new Min Line, Max Line, etc. (step 4)? Also, double-check the formatting of the line in case its color blends with the background of your visual (step 5). Good luck! Don't forget to flag a a solution as it may help others.

[Anonymous]

Hi @marcoselias could you please attach a sample file with implementation if possible..would be very helpful

[Anonymous]

Very clever!  

[Anonymous]

Thanks, I can't believe this worked and did exactly what I was looking for!  One point of clarification I would add is with step 3.  I had to set the value of the visual to the measure as I could not figure out how to "Add the measure with the constant value to your list of values of the visual".  It set all of the bars to the measure but, once I went to Analytics and set the max line to this measure, I was able to set the original value back in the visual and the max line stayed set to the measure.

[v-yuta-msft]

@miltenburger,

---

What I want, when I choose for another year in the filter, my budget (constant line) changes automatically. Is there a possibility to achieve this?

---

The "constant line" you mentioned is the constant line option in Analytics pane, right? If it is, what does "based on a hardcoded budget" mean? Could you clarify more details about the cuurent settings and your expected result?

Regards,

Jimmy Tao

[miltenburger]

Thanks @v-yuta-msft for your reply.

The constant line in the analytics pane that's right.

How i implemented the constant line right now:

My value in column "Waarde" is hardcoded. But actually I want this value to change dynamicaly when I change the year filter on my report. Is there a possibility for this?

[v-yuta-msft · Accepted solution]

@miltenburger,

I'm afraid this couldn't be achieved because the value of constant line is determined by constant value, it can't be changed by slicer. As a workaround. I would recommend you to create a measure using DAX like pattern:

Measure =
SELECTEDVALUE ( table[value] )

And then drag the measure to your chart.

Regards,

Jimmy Tao