cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
moltra
Helper IV
Helper IV

How to show goal lines on a line chart

I have a table that is created by summarizing data from a large table on a weekly basis.  I am graphing the summarized table on a line graph. I would like to have 2 "Goal lines" displayed on the graph.  One would be 30% of the MAX value of one of the fields in the Summarized table.  The other would be 10% of the MAX value.  

 

I have tried multiple ways of doing this, but either get a circular error warning or the MAX field data changes based on date and time.

 

Any information or guidance would be great.  I am working on a way to show you what I am wanting to do, just have to ensure I am not sharing internal data or processes.

 

This is what I am getting if I try 

MAXX ( VALUES ( Table[ColumnonXAxis] ), [Measure used on line chart] ) * .30
ReadingDate timename30%percent
99212/31/2019 0:00widget19.2
10841/1/2020 0:00widget21
27161/2/2020 0:00widget55.5
25961/3/2020 0:00widget54
10491/4/2020 0:00widget13.5
15861/5/2020 0:00widget51.6
31511/6/2020 0:00widget54.9
31691/7/2020 0:00widget56.1
28576/3/2020 0:00widget53.7
28836/4/2020 0:00widget54
15376/5/2020 0:00widget52.2
10936/6/2020 0:00widget14.1
10226/7/2020 0:00widget13.2
25606/8/2020 0:00widget54
29136/9/2020 0:00widget54
29046/10/2020 0:00widget53.7
29056/11/2020 0:00widget53.1
10186/12/2020 0:00widget57.6

 

I need to calculate like this. 30%Percent = 3169 * .3 = 950.7

ReadingDate timename30%percent
99212/31/2019 0:00widget950.7
10841/1/2020 0:00widget950.7
27161/2/2020 0:00widget950.7
25961/3/2020 0:00widget950.7
10491/4/2020 0:00widget950.7
15861/5/2020 0:00widget950.7
31511/6/2020 0:00widget950.7
31691/7/2020 0:00widget950.7
28576/3/2020 0:00widget950.7
28836/4/2020 0:00widget950.7
15376/5/2020 0:00widget950.7
10936/6/2020 0:00widget950.7
10226/7/2020 0:00widget950.7
25606/8/2020 0:00widget950.7
29136/9/2020 0:00widget950.7
29046/10/2020 0:00widget950.7
29056/11/2020 0:00widget950.7
10186/12/2020 0:00widget950.7
2 ACCEPTED SOLUTIONS

@moltra here it is. I appreciate your patience.

 

10%goal = 
VAR _max=MAXX(ALLSELECTED('Table'),'Table'[Reading])
Return
(_max*0.1) * DIVIDE ( SUM ( 'Table'[Reading]), SUM('Table'[Reading]) )

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

@moltra so the first part you know what it is the new part we added is the DIVIDE

 

before divide we are getting the result of measure for each selected Date even that date has any reading or not, with divide we get the sum of reading for that day and the date where there is no reading we will get blank(), by dividing blank with blank we get blank value and multiplying it with (_max*0.1) will convert it to blank.

 

but the other days where there is a reading, let's say on a Jan 04th, the sum of reading is 10, so dividing 10 with 10 will give us 1 and multiplying (_max*0.1) with 1 will return the same value

 

Basically from DIVIDE we are getting the value of BLANK or 1 based on the reading for that day  and in case of blank() our measure is returning blank value and it doesn't show up in the visual. I hope it all makes sense if not, let me know and I will try to explain it further.

 

 

10%goal = 
VAR _max=MAXX(ALLSELECTED('Table'),'Table'[Reading])
Return
(_max*0.1) * DIVIDE ( SUM ( 'Table'[Reading]), SUM('Table'[Reading]) )

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

23 REPLIES 23
Pragati11
Super User III
Super User III

Hi @moltra ,

 

If you click on your Line Chart visual, under Visualisations Pane, you see the following options:

 

line1.png

 

You can define here Min, Max reference lines.

 

Thanks,

Pragati

 


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




I need a 10% line and a 30 percent line of the MAX value.  Please look at the edited request above.

@moltra did you tried the solution I proposed, you need to create two more measures to get this going. Since I don't know your data, I just gave you the expression which you need to apply to your model.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





I tried your fomula it is gave me the data in the 30%goal_Incorrect column.  I need what is showing in the 30%goal_correct column.

 

question_table.png

Hi @moltra

 

Create 2 measures as below:

 

10%goal = 
VAR _max=MAXX(ALL('Table'),'Table'[Reading])
Return
_max*0.1
30%goal = 
VAR _max=MAXX(ALL('Table'),'Table'[Reading])
Return
_max*0.3

 

And you will see:

Annotation 2020-06-15 134128.png

For the related .pbix file, pls click here.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

@v-kellya-msft 

This works great, but if I select a different name in the Slicer, it does not update the 10% or 30% goals.

 

I just realized that for somereason it is changing the X axis from 7 days to over 5 months.

@moltra try to change the measures proposed by @v-kellya-msft from ALL to ALLSELECTED.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2k   That corrected the values so that the slicer changes them.

 

But still having the issue of the X axis changing when I add the measures.

@moltra no sure what you mean by x-axis changes. can you share the screenshot of what you are referring to?

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2k Here is what the chart looks like without the 10%goal.  I looked at the data as a table and it is filling in the data for the 10%goal for all the data in the main table not just the time frame selected.

No MeasuresNo Measures

Here is what it looks like if I add the 10%Goal measure to the chart.

MeasuresMeasures

@moltra got it, change both the measures like this

 

10%goal = 
VAR _max=MAXX(ALL('Table'),'Table'[Reading])
Return
(_max*0.1) * DIVIDE ( SUM ( 'Table'[Reading]), SUM('Table'[Reading]) )

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





That worked after I changed the ALL to ALLSELECTED.  But I do not see how it is removing the data that is not needed.

@moltra so everything is working or not and then I can explain what was the fix.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2k Yes, Everything is working properly now.  If you change the all to the allselected I will accept as solution.

@moltra its bit of confusing statement, you changed from ALL To ALLSELECTED correct? What is not working? You are over complicating things or maybe I'm not getting it.

 

You have to tell precisely what your measures are, what is not working and what is your expectation?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2k   Will the ALL in the measure the data is not refreshing when I use the name slicer to select a different Name. 

10%goal = 
VAR _max=MAXX(ALL('Table'),'Table'[Reading])
Return
(_max*0.1) * DIVIDE ( SUM ( 'Table'[Reading]), SUM('Table'[Reading]) )

 

When I change the ALL to ALLSELECTED the measure changes when I select a different name in the name slicer.

10%goal = 
VAR _max=MAXX(ALLSELECTED('Table'),'Table'[Reading])
Return
(_max*0.1) * DIVIDE ( SUM ( 'Table'[Reading]), SUM('Table'[Reading]) )

@moltra so why you are using ALL , you need to use ALLSELECTED , and your problem statement is still not clear. Forget ALL for now and then tell what is not working.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Everything is working correctly now, I was asking you to update your solution from ALL to ALLSELECTED so I could accept it as the solution.

@moltra here it is. I appreciate your patience.

 

10%goal = 
VAR _max=MAXX(ALLSELECTED('Table'),'Table'[Reading])
Return
(_max*0.1) * DIVIDE ( SUM ( 'Table'[Reading]), SUM('Table'[Reading]) )

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

@parry2k Can you explain how your formula works.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

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

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors