Forum Discussion
Helper IIplot different measures (y-axis) against different quarters (x-axis) in a single line in Line Chart
I am fetching data from 4 excel files from Sharepoint folder into Power BI. Along with other data all files has a completion Date column and sales amount. Each file represent a Quarter and consider name of files such as 2023Q4, 2024Q1, 2024Q2, 2024Q3. I need to plot this data in 2 line charts as below:
1st Line Chart:
X axis - Quarter (From Date table which is mapped to Completion Date in Quarterly Files)
Y-Axis - Average of sales amount for that quarter
Average of sales amount from excel file 2023Q4 will be calculated using measure and plot in line chart against Quarter Q4-2023.
Similarly average of sales amount from file 2024Q1 will be plotted against quaerter Q1-2024 and average of sales amount from file 2024Q2 will be plotted against quaerter Q2-2024 in line chart.
Since current quarter ( Q3-2024) is not completed yet, there are other filters in the file 2024Q3 to calculate Projected value of average sales amount, which will be plotted against Q3-2024.
I need to create measure to calcualte average of sales amount for each quarter from each dataset/file and plot on respective quarters.
I simply can't combine all 4 files, create claculated column and then drag and drop values because need to set more filters to calculate projected value for current quarter. but still any suggestions is welcome please.
2nd Line chart:
same scenario as above, but instead of single line , now this line chart will show 4 different lines in representing 4 different categories. Seems once first scenario can be achieved , this also is possible by just dragging and dropping category field on to Line chart. which we generally achieve in simple line chart but not sure.
Any quick solution, help, suggestions, tip & tricks is most welcome.
7 Replies
- lbendlin
Super User
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 
DataSpacelbendlin I can't post actual data but created similar sample data in excel.
here is the google drive link to access excel data:
To calculate Average sales amount for 4th Quarter 2023, use sheet 2023Q4 :
1. Filter Completion Date between 2023/10/01 to 2023/12/31
2. Transaction Type = Completed
3. Demand Status = Fulfilled
( as per sample data, Average sales amount for 2023Q4 = $65.29 )
To calculate Average sales amount for 1st Quarter 2024, use sheet 2024Q1 :
1. Filter Completion Date between 2024/01/01 to 2024/03/31
2. Transaction Type = Completed
3. Demand Status = Fulfilled
( Average sales amount for 2024Q1 = $62.94 )
To calculate Average sales amount for 2nd Quarter 2024, use sheet 2024Q2 :
1. Filter Completion Date between 2024/04/01 to 2024/06/30
2. Transaction Type = Completed
3. Demand Status = Fulfilled
( Average sales amount for 2024Q2 = $68.36 )
Since 3rd Quarter 2024 is still running, it will have different filters to calculate "Projected average of sales amount:
1. Filter Completion Date between 2024/07/01 to 2024/09/30
2. It will consider ALL transaction Type
3. It will consider ALL Demand Status
(Projected average of sales amount for 2024Q3: $73.92)
Expected out for 1st Line Chart should be similar to as per below image:
For 2nd Line chart, we can drag Category column and drop on this line chart which will give 4 different lines in the same Line chart representing each category and providing Average sales amount against Quarter.
Let me know if any further info is needed.
- lbendlin
Your sample data yields different results.
- DataSpace
it giving different results because you have not considered Complete Date as filter. Completion Data shouls also be taken into consideration to filter data for respective Quarter. Particular Quarter file canalso contain data from other quarters.
