Fixed Average in Power BI

[HenokS]

Hi, 

I am trying to create a fixed average in power bi. The desired output is to have a straight line  on the visual regardless of the month on the x axis.

Formula I am using

FY Average = calculate(Averagex(Allselected(Table, MeasureTable'[fyamount],FILTER(Table,Table'[ColumnName] = "xxxxx"))

YYYYMMM     FY Average (Desired)        FY Average (current results)

2021001         75422                                15632

2021002         75422                                12645

2021003         75422                                 35154

2021004         75422                                 32645

[parry2k · Accepted solution]

@HenokS tweak it a bit

FY Average = Averagex(Allselected(WPTDataTable),CALCULATE([fyamount], KEEPFILTERS( WPTDatatable'[Plan Type] = "Forecast")) )

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[parry2k]

@HenokS try this measure

FY Average = Averagex(Allselected(WPTDataTable),CALCULATE([fyamount],WPTDatatable'[Plan Type] = "Forecast"))

 

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[HenokS]

Thanks, The average is spreading to future years and transactions that dont have Plan type = "Forecast" . Is there a way to limit it to only those that have plan type "Forecast"

[HenokS]

FirstFormula, FYAmount ( I created a measure for the amount) under the measure table.

FYAmount = Sum(WPTDataTable)'[Amount])

Second Formula- created for the average
FY Average = calculate(Averagex(Allselected(WPTDataTable,MeasureTable'[fyamount],FILTER(WPTDataTable,WPTDatatable'[Plan Type] = "Forecast"))

[parry2k]

@HenokS your formula doesn't make sense, can you paste sample data.