Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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!
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:) .
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 ?
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!
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!
@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
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.
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!
@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