topic Re: Bar chart with the last 12 months and the total average in DAX Commands and Tips

Bar chart with the last 12 months and the total average

Rai_Lomarques — Sun, 16 Mar 2025 14:06:43 GMT

Hello friends,
Can you tell me how I can achieve the same result as the graph in the image below? I need to create a graph in Power BI where on the X axis I have the last 12 months prior to the month that is being filtered in the data segmentation, the months need to be in the "MM/YYYY" format. On the same X axis I need to have a column for the AVERAGE of these last 12 months.

When the user filters the month "09/2024" in the segmentation, then the graph should show the last 12 months prior to "09/2024"

Re: Bar chart with the last 12 months and the total average

lbendlin — Sun, 16 Mar 2025 19:47:29 GMT

Showing the same data twice in a visual is a design red flag. Consider using an average line instead of the column.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Bar chart with the last 12 months and the total average

Deku — Sun, 16 Mar 2025 21:00:42 GMT

as mentioned by lbendlin you could include a average line

Avg Amount = AVERAGEX( ALLSELECTED( 'dates'[Year Month] ) ,[Amount] )

If you really want that extra bar.

I created a date dimension

dates = ADDCOLUMNS( CALENDARAUTO() ,"Year", YEAR( [Date] ) ,"Year Month", EOMONTH( [Date] ,0 ) ,"Year Month No", (YEAR( [Date] ) * 100) + MONTH( [Date] ) ,"Month", MONTH( [Date] ) )

and a table for the axis

xAxis = UNION( SELECTCOLUMNS( SUMMARIZE( 'dates', 'dates'[Year Month], dates[Year Month No] ) ,"Index", dates[Year Month No] ,"Label", FORMAT( dates[Year Month], "mm/yyyy" ) ,"Hex", "#0f3057" ) ,ROW( "Index", 999999, "Lebel", "Average", "Hex", "#604e82" ) )

Added a mesure for the y axis

Amount With Avg = IF( SELECTEDVALUE( xAxis[Label] ) = "Average" ,[Avg Amount] ,CALCULATE( [Amount] ,TREATAS( VALUES( xAxis[Index] ) , dates[Year Month No] ) ) )

and a measure for conditional formatting on the bars

Colour = SELECTEDVALUE( xAxis[Hex] )

Re: Bar chart with the last 12 months and the total average

Anonymous — Tue, 18 Mar 2025 01:31:50 GMT

Hi Rai_Lomarques ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

Re: Bar chart with the last 12 months and the total average

V-yubandi-msft — Thu, 24 Apr 2025 08:49:19 GMT

Hi Rai_Lomarques ,

Has your issue been resolved, or do you require any further information? Your feedback is valuable to us. If the solution was effective, please mark it as 'Accepted Solution' to assist other community members experiencing the same issue.

Re: Bar chart with the last 12 months and the total average

V-yubandi-msft — Mon, 28 Apr 2025 15:28:37 GMT

Hi Rai_Lomarques ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

Re: Bar chart with the last 12 months and the total average

V-yubandi-msft — Fri, 02 May 2025 10:30:51 GMT

Hi Rai_Lomarques ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.