How to add goal for each year in the Column line chart ?

[Anonymous]

Hi Community,

I am not able to add the goal (in percentage) to the existing Column and line chart . There are 3 different years ( May 2021, May 2022 and May 2023 ) for which data is aggregated as shown in screen shot below. 

I want a line to be inserted based on the values shown below. I basically do not have a column which represents Goal % for every year. Could some one please help me on how to acheive this ? Please assist .

Goal%   Year 

25        May 2021

50        May 2022

100      May 2023 

Thanks,

G Venkatesh

[Anonymous]

Sorry for the late reply. Thanks @amitchandak and @v-kelly-msft . Both the solutions worked for me and i am able to use it in my report now:) . 

[Anonymous]

I have a question lately and sorry to post it in the same link . As shown in the screen shot, I am able to show the values as per the requirement and Goal line is coming up corectly. However, I am unable to add May 2021's data to May 2022 and then May 2021+May 2022 data should be added to May 2023 ( Some thing like rolling measure) . 

Could some one please assist me on this ?

[Syndicate_Admin]

Don't @vgarlap,

Create a measure as follows:

Measure = 
CALCULATE(SUM('Table'[Actual]),FILTER(ALL('Table'),'Table'[Month Year]<=MAX('Table'[Month Year])))

Then place the measure in the column field and you will see:

For the related .pbix file, see attachment pls.

Saludos
Kelly

Did I answer your question? Mark my position as a solution!

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

Create a measure as below:

Measure = 
CALCULATE(SUM('Table'[Actual]),FILTER(ALL('Table'),'Table'[Month Year]<=MAX('Table'[Month Year])))

Then put the measure in the column field and you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

[amitchandak]

@Anonymous , You need to create a target/goal table and join it with the common date/month/year table of actual and show them together

[Anonymous]

Hi Amit,

I have created a new Goal table some thing like this as shown below  and joined it with main table using Month Year.  However, when I am using Goal column in line section ., it is either asking me to do count or some aggregation and showing wrong values. 

Please assist. 

[v-kelly-msft]

Hi @Anonymous,

Set the same start and end value for both column and line values:

Using "Sum" for aggregation and and you will see:

Check my .pbix file attached.

Best Regards,
Kelly

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

[amitchandak]

@Anonymous , you are joining both of them to common date/month year table 

watch few mins of this video till I connect target with the common date table

https://www.youtube.com/watch?v=yPQ9UV37LOU