Forum Discussion
DAX Question - Interesting
Hello All,
I have an Interesting question –
In my data, I have fuel producer, fuel purchaser, contract start date, contract duration, and total volume for the contract duration.
How do I show a bar chart with fuel produced each year?
DATE | FUEL PRODUCER | FUEL PURCHASER | TOTAL VOLUME ( MILLIONS OF LITERS) | DURATION OF AGREEMENT (YEARS) |
Sep 24, 2014 | ABC | United Airlines | 10 | 1 |
Aug 7, 2015 | PQR | Delta | 36 | 3 |
I will greatly appreciate your input.
Thanks,
Pav
Hi,
Please find attached the PBI file.
Hope this helps.
16 Replies
PavB what is the expected output? Is that total volume equally distributed to all the agreement years? Provide details that help to answer the question, without the business logic, not sure how one can assist. Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Hi Parry,
Expected output is below.
Year FUEL PRODUCER FUEL PURCHASER TOTAL VOLUME ( MILLIONS OF LITERS) DURATION OF AGREEMENT (YEARS) 2014 ABC United Airlines 10 1 2015 PQR Delta 12 3 2016 PQR Delta 12 3 2017 PQR Delta 12 3
We divide the total fuel volume with the duration of the contract, and add records for each successive year for that producer and purchaser for the avg fuel per year.
I hope this helps.
Thanks,
Pav
i hope this explains it a little better -
Interesting question –
In my data, I have fuel producer, fuel purchaser, contract start date, contract duration, and total volume for the contract duration.
How do I show a bar chart with fuel purchased each year?
If Delta is signing an agreement in 2015, to purchase 36 Million liters over 3 years, then that should reflect as 12 million in 2015, 12 million in 2016, and 12 million in 2017.
AGREEMENT DATE
FUEL PRODUCER
FUEL PURCHASER
TOTAL VOLUME ( MILLIONS OF LITERS)
DURATION OF AGREEMENT (YEARS)
Sep 24, 2014
ABC
United Airlines
10
1
Aug 7, 2015
PQR
Delta
36
3
Expected output for data manipulation is below. Below data will be used to create a bar chart.
Year
FUEL PRODUCER
FUEL PURCHASER
TOTAL VOLUME ( MILLIONS OF LITERS)
DURATION OF AGREEMENT (YEARS)
2014
ABC
United Airlines
10
1
2015
PQR
Delta
12
3
2016
PQR
Delta
12
3
2017
PQR
Delta
12
3
We divide the total fuel volume with the duration of the contract, and add records for each successive year for that producer and purchaser for the avg fuel per year.
I hope this helps.
Is it better to do this in SQL?
Thanks,
Pav
Hi Parry2k,
The bar chart you are showing in the Fuel.pbix is correct.
And yes, the total fuel volume is divided equally for all years in the duration of the contract.
My question is, given the input data below, 2 recrods, how do we create 4 records (which is the right thing to do) to create the bar chart.
Since real data will have many rows, need to do it automatically (dax or power query), not manually.
Thanks again!
Pav
YEAR FUEL PRODUCER FUEL PURCHASER TOTAL VOLUME ( MILLIONS OF LITERS) DURATION OF AGREEMENT (YEARS) 2014 ABC United Airlines 10 1 2015 PQR Delta 36 3 If you pay attention to the table which produced the bar chart, it is already producing the rows in the PQ. You need to out some efforts to look and explore the file before asking these questions. Thanks!
Excellent!!
Thank You!
Pav
To create a bar chart showing fuel produced each year, you can follow these steps using DAX in Power BI or a similar tool:
Data Model Setup: First, ensure that your data is loaded into your data modeling tool (like Power BI) with the necessary columns: DATE, FUEL PRODUCER, FUEL PURCHASER, TOTAL VOLUME, and DURATION OF AGREEMENT.
Create a Calendar Table: If you don't already have a calendar table, create one. This table will help you create a relationship between dates and the contracts. It's essential for time-based calculations.
Define Relationships: Ensure that there's a relationship between the DATE column in your main data table and the date column in the calendar table.
Create a Measure: You'll need to create a DAX measure that calculates the total volume produced each year. Here's an example of how to do that:
Total Volume Per Year =
SUM('YourTableName'[TOTAL VOLUME])Replace 'YourTableName' with the actual name of your data table.
Create a Bar Chart: Now, create a bar chart visualization. Place the DATE column from your calendar table on the X-axis and the 'Total Volume Per Year' measure on the Y-axis.
Filtering: You can add slicers or filters for FUEL PRODUCER and FUEL PURCHASER to allow users to filter the data based on these criteria if needed.
Your bar chart should now display the fuel produced each year based on your data. The 'Total Volume Per Year' measure calculates the sum of total volume for each year, taking into account any filtering or grouping by FUEL PRODUCER and FUEL PURCHASER.
This setup assumes that you have a calendar table and a proper data model. If you don't have a calendar table, you can create one manually or use DAX functions like CALENDAR to generate one. The key to successful time-based calculations in DAX is having a well-defined date/calendar table and establishing relationships between it and your data tables.
