How to show goal lines on a line chart

[moltra]

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

Reading	Date time	name	30%percent
992	12/31/2019 0:00	widget	19.2
1084	1/1/2020 0:00	widget	21
2716	1/2/2020 0:00	widget	55.5
2596	1/3/2020 0:00	widget	54
1049	1/4/2020 0:00	widget	13.5
1586	1/5/2020 0:00	widget	51.6
3151	1/6/2020 0:00	widget	54.9
3169	1/7/2020 0:00	widget	56.1
2857	6/3/2020 0:00	widget	53.7
2883	6/4/2020 0:00	widget	54
1537	6/5/2020 0:00	widget	52.2
1093	6/6/2020 0:00	widget	14.1
1022	6/7/2020 0:00	widget	13.2
2560	6/8/2020 0:00	widget	54
2913	6/9/2020 0:00	widget	54
2904	6/10/2020 0:00	widget	53.7
2905	6/11/2020 0:00	widget	53.1
1018	6/12/2020 0:00	widget	57.6

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

Reading	Date time	name	30%percent
992	12/31/2019 0:00	widget	950.7
1084	1/1/2020 0:00	widget	950.7
2716	1/2/2020 0:00	widget	950.7
2596	1/3/2020 0:00	widget	950.7
1049	1/4/2020 0:00	widget	950.7
1586	1/5/2020 0:00	widget	950.7
3151	1/6/2020 0:00	widget	950.7
3169	1/7/2020 0:00	widget	950.7
2857	6/3/2020 0:00	widget	950.7
2883	6/4/2020 0:00	widget	950.7
1537	6/5/2020 0:00	widget	950.7
1093	6/6/2020 0:00	widget	950.7
1022	6/7/2020 0:00	widget	950.7
2560	6/8/2020 0:00	widget	950.7
2913	6/9/2020 0:00	widget	950.7
2904	6/10/2020 0:00	widget	950.7
2905	6/11/2020 0:00	widget	950.7
1018	6/12/2020 0:00	widget	950.7

[parry2k · Accepted 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.⚡

[parry2k · Accepted solution]

@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.⚡

[moltra]

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

[parry2k]

@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.⚡