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

@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

@parry2k That is what I though you were doing.  Thanks for the help.

parry2k
Super User III
Super User III

@moltra share sample data but in the meantime try this

 

create two measures

 

30 Percent Line = 
MAXX ( VALUES ( Table[ColumnonXAxis] ), [Measure used on line chart] ) * .30

10 Percent Line = 
MAXX ( VALUES ( Table[ColumnonXAxis] ), [Measure used on line chart] ) * .10

 

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.





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