Bar chart with the last 12 months and the total average

[Rai_Lomarques]

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"

[V-yubandi-msft]

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.

[V-yubandi-msft]

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.

[V-yubandi-msft]

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.

[Anonymous]

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

[Deku]

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] )

[lbendlin]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...