Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi All,
End Solution Expected: I want to show Avg Price on Line chart for each project based on duration of contract
Fields I have as per below sample table
Project Name | Avg. Price per Year | Total Contract Year |
Project a | £ 20000 | 20 |
Project b | £ 30000 | 15 |
Project c | £ 45000 | 25 |
Project d | £ 65000 | 20 |
Project e | £ 25000 | 12 |
Project n | £ 35000 | 15 |
Any idea how to achieve above graphical representation based on these three fields
Solved! Go to Solution.
Hi @AVS218 ,
Based on the sample data you provided,
please see the attached file.
The result is based on the expected output you provided.
Hope this idea helps.
Hi @AVS218 ,
Based on the sample data you provided,
please see the attached file.
The result is based on the expected output you provided.
Hope this idea helps.
Thanks a lot... It helped us to solve major challenge we had and kill alternative approach used which was very time consuming. Post implementation of that solution, we came across a scenario where we have some projects with values split by year and some consolidated ... See example below. Is it possible to generate same visualisation when you have some projects captured as one line and some in multiple lines with unique line value
Visualisation we are trying to achieve is one by contract start year and one based on solution you already provided
Project Name | Contract Start Date | Contract End Date | Contract Term (Years) | Avg. Price per year |
Project A | 31/03/2021 | 31/03/2026 | 5 | 40000 |
Project B | 25/11/2021 | 24/11/2026 | 5 | 42000 |
Project C | 25/04/2023 | 24/04/2024 | 20 | 50000 |
Project C | 24/04/2024 | 24/04/2025 | 20 | 51000 |
Project C | 24/04/2025 | 24/04/2026 | 20 | 60000 |
Project C | 24/04/2026 | 24/04/2027 | 20 | 60000 |
Project C | 24/04/2027 | 24/04/2028 | 20 | 60000 |
Project C | 24/04/2028 | 24/04/2029 | 20 | 60000 |
Project C | 24/04/2029 | 24/04/2030 | 20 | 60000 |
Project C | 24/04/2030 | 24/04/2031 | 20 | 60000 |
Project C | 24/04/2031 | 24/04/2032 | 20 | 60000 |
Project C | 24/04/2032 | 24/04/2033 | 20 | 60000 |
Project C | 24/04/2033 | 24/04/2034 | 20 | 65000 |
Project C | 24/04/2034 | 24/04/2035 | 20 | 65000 |
Project C | 24/04/2035 | 24/04/2036 | 20 | 65000 |
Project C | 24/04/2036 | 24/04/2037 | 20 | 65000 |
Project C | 24/04/2037 | 24/04/2038 | 20 | 65000 |
Project C | 24/04/2038 | 24/04/2039 | 20 | 66000 |
Project C | 24/04/2039 | 24/04/2040 | 20 | 66000 |
Project C | 24/04/2040 | 24/04/2041 | 20 | 67000 |
Project C | 24/04/2041 | 24/04/2042 | 20 | 67000 |
Project C | 24/04/2042 | 24/04/2043 | 20 | 67000 |
Project D | 31/03/2021 | 31/03/2026 | 5 | 53000 |
Project E | 01/12/2021 | 01/12/2022 | 20 | 75000 |
Project E | 01/12/2022 | 01/12/2023 | 20 | 75000 |
Project E | 02/12/2021 | 01/12/2024 | 20 | 75000 |
Project E | 01/12/2024 | 01/12/2025 | 20 | 75000 |
Project E | 03/12/2021 | 01/12/2026 | 20 | 75000 |
Project E | 01/12/2026 | 01/12/2027 | 20 | 75000 |
Project E | 04/12/2021 | 01/12/2028 | 20 | 75000 |
Project E | 01/12/2028 | 01/12/2029 | 20 | 75000 |
Project E | 05/12/2021 | 01/12/2030 | 20 | 75000 |
Project E | 01/12/2030 | 01/12/2031 | 20 | 75000 |
Project E | 06/12/2021 | 01/12/2032 | 20 | 75000 |
Project E | 01/12/2032 | 01/12/2033 | 20 | 75000 |
Project E | 07/12/2021 | 01/12/2034 | 20 | 75000 |
Project E | 01/12/2034 | 01/12/2035 | 20 | 75000 |
Project E | 08/12/2021 | 01/12/2036 | 20 | 75000 |
Project E | 01/12/2036 | 01/12/2037 | 20 | 75000 |
Project E | 09/12/2021 | 01/12/2038 | 20 | 75000 |
Project E | 01/12/2038 | 01/12/2039 | 20 | 75000 |
Project E | 10/12/2021 | 01/12/2040 | 20 | 75000 |
Project E | 01/12/2040 | 01/12/2041 | 20 | 75000 |
Project F | 01/12/2021 | 01/12/2041 | 20 | 59000 |
Project F | 01/12/2021 | 01/12/2041 | 20 | 59000 |
Project F | 01/12/2021 | 01/12/2041 | 20 | 59000 |
Project F | 01/12/2021 | 01/12/2041 | 20 | 59000 |
Project F | 01/12/2021 | 01/12/2041 | 20 | 59000 |
Project F | 01/12/2021 | 01/12/2041 | 20 | 59000 |
Project F | 01/12/2021 | 01/12/2041 | 20 | 59000 |
Project F | 01/12/2021 | 01/12/2041 | 20 | 59000 |
Project F | 01/12/2021 | 01/12/2041 | 20 | 59000 |
Project F | 01/12/2021 | 01/12/2041 | 20 | 59000 |
Project F | 01/12/2021 | 01/12/2041 | 20 | 59000 |
Project F | 01/12/2021 | 01/12/2041 | 20 | 59000 |
Project F | 01/12/2021 | 01/12/2041 | 20 | 59000 |
Project F | 01/12/2021 | 01/12/2041 | 20 | 59000 |
Project F | 01/12/2021 | 01/12/2041 | 20 | 59000 |
Project F | 01/12/2021 | 01/12/2041 | 20 | 59000 |
Project F | 01/12/2021 | 01/12/2041 | 20 | 59000 |
Project F | 01/12/2021 | 01/12/2041 | 20 | 59000 |
Project F | 01/12/2021 | 01/12/2041 | 20 | 59000 |
Project F | 01/12/2021 | 01/12/2041 | 20 | 59000 |
Super thanks for the solution. Really Appreciate it. It helped sort out main issue and gave us endless possibility
Your sample data is missing the date/year column.
Apologies I missed on that... Data will look like below and I have to consider Contract Start Date as Year 1 and then build on that based on Total Contract Duration. i.e. for Project A Contract Start Year as per date is 2019 and duration is 20 years. so my line chart should display £20k price from 2019 to 2039 and so on for other projects.
Eventually we would like to visualize in two ways one displayed in my previous message and one which will show how much all projects cumulatively earn revenue in a specific year e.g. 2025
Project Name | Avg. Price per Year | Contract Start Date | Total Contract Duration |
Project a | £ 20000 | 20/07/2019 | 20 |
Project b | £ 30000 | 20/01/2020 | 15 |
Project c | £ 45000 | 20/05/2020 | 25 |
Project d | £ 65000 | 25/10/2020 | 20 |
Project e | £ 25000 | 24/02/2021 | 12 |
Project n | £ 35000 | 22/05/2021 | 15 |
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
9 | |
9 | |
7 | |
6 | |
5 |
User | Count |
---|---|
20 | |
11 | |
10 | |
9 | |
6 |