Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello All,
I am a beginner with Power BI.
I have a column with date and another column with values. I would like to generate a bar graph beginning with previous years average bar follwed by current months average bars. Something like below.
How can I acheive this using Power BI. Any help would be appreciated!
Thankyou!
Solved! Go to Solution.
Hi @mokhan ,
Based on your description, you can create a calulated column like this to use it as X-axis:
Axis =
IF (
YEAR ( 'Table'[Date] ) = YEAR ( TODAY () ),
'Table'[Date].[Month],
CONVERT ( YEAR ( 'Table'[Date] ), STRING )
)
Then create these to measures, one of them calculates average about each year and each month for current year, another one calulates average about all years as a target:
Avg = AVERAGE('Table'[Values])
Avg_allyears =
CALCULATE ( AVERAGE ( 'Table'[Values] ), ALL ( 'Table' ) )
Use a 'Line and stacked column chart ' to show it:
Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @mokhan ,
Based on your description, you can create a calulated column like this to use it as X-axis:
Axis =
IF (
YEAR ( 'Table'[Date] ) = YEAR ( TODAY () ),
'Table'[Date].[Month],
CONVERT ( YEAR ( 'Table'[Date] ), STRING )
)
Then create these to measures, one of them calculates average about each year and each month for current year, another one calulates average about all years as a target:
Avg = AVERAGE('Table'[Values])
Avg_allyears =
CALCULATE ( AVERAGE ( 'Table'[Values] ), ALL ( 'Table' ) )
Use a 'Line and stacked column chart ' to show it:
Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@mokhan You asked this in the Power Query forum. This is probably better asked in the Desktop forum. But that won't stop people from trying to help you with your DAX
If I understand correctly, you would first need to get the MONTHLY SUM of your Metric, and from there, get the AVERAGE of those sums for the LAST YEAR. Is that correct?
Start with two calculated columns in your table: YEAR(Date) and MONTH(Date):
TheYear = YEAR([Date]) and TheMonth = MONTH([Date])
Then add a derived table in DAX:
Monthly Sums = SUMMARIZE ( <fact table name>, [TheYear], [TheMonth], "Sum of Metric", SUM( [Some column] ) )
Refer to this for SUMMARIZE: SUMMARIZE function (DAX) - DAX | Microsoft Docs
Finally, create a MEASURE as:
Average Monthly = AVG ( 'Montly Sums' [Sum of Metric] )
Hope this helps
Proud to be a Super User! | |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.