Forum Discussion
Fixed Average in Power BI
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
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.
5 Replies
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.
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 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.
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"))
