Dynamic target vs Year

[richard24best]

Hi Team,

I have a requirement to create a table with Target vs Achieved along with year as filter. Achieved gets calculated based on Target values populated in a table(i can take care of that).

Can someone advise how can i get the Yearly Target values populated based on user selecting Year in a table?

Attached file for your reference.

Regards,

Richard

[sarafoster]

You can achieve this by creating a pivot table with the Target values and using a slicer to filter the data by year. Follow these steps:

1. Create a table with columns for Year and Target.
2. Enter the Yearly Target values in the Target column for each year.   Home Bargains Portal
3. Create a pivot table with the Year column in the Rows area and the Target column in the Values area.
4. Add a slicer to the pivot table for the Year column, allowing the user to select a specific year.
5. Create a separate table for Achieved values, with columns for Year and Achieved.
6. Calculate the Achieved values based on the Target values populated in the first table.
7. Create a visualization such as a column chart that shows the Achieved values for the selected year alongside the Target value for that year.

When the user selects a year in the slicer, the pivot table will update to show the Target value for that year. You can use this value to calculate the Achieved value and display it in the visualization.

[sarafoster]

You can create a parameter in your table that allows users to select the year they want to see. Then, you can use this parameter to filter your data source to only include data for that year. You can then calculate the yearly target value by summing the target values for that year. Finally, you can calculate the achieved value based on the filtered data source and the calculation method you have in mind.

[richard24best]

Hi Sarafoster, 

Thanks for your advice. It worked when i changed the year and targets into rows and columns.

But any idea how can i populate field targets even if the there is no data for that year. I mean i want the target to show even if there is no volumes. Snapshot attached for your reference where some fields target is showing as blank/null as they dont have volumes.

Appreciate your help in advance.

Regards,

Richard