topic Re: Matrix Average Column in DAX Commands and Tips

Matrix Average Column

spandy34 — Fri, 11 Mar 2022 09:24:04 GMT

I would like to add a Monthly Average as a column at the end of this table and I was wondering if anyone could help please?

The columns are Calender Months and the values are the Count of Claim Refs

Re: Matrix Average Column

Anonymous — Fri, 11 Mar 2022 10:06:15 GMT

Hi spandy34
as a workaround, you can calculate the averages for each fiscal year in a measure and place them in a card visual next to the respective rows.
You can calculate the average Count of Claim Refs per fiscal year by using Calculate() function. For Example:

Average Count of Claim Refs for 2021-2022 = Calculate(Average([Claim Refs]), 'Table'[Fiscal Year] = "2021-2022")

Similarly for fiscal years 2020-2021 and 2019-2020.

Regards

Re: Matrix Average Column

v-chenwuz-msft — Tue, 15 Mar 2022 07:55:50 GMT

Hi spandy34 ,

Matrix table is not support to add a column by custom. The only way to add a column is add a new row under Calender Months, which value is "average".

Then measure:

Measure =

var _average = calculate(AVERAGE('Table'[values]),REMOVEFILTERS('Months column'))

return

IF(SELECTEDVALUE('Months column'[month])="average",_average,SUM('Table'[values]))

Result:

The same problem has been solved in this post you can refer.

Solved: Re: Dax to get YoY change - Microsoft Power BI Community

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Matrix Average Column

Anonymous — Mon, 21 Oct 2024 07:32:43 GMT

Hi, chenwu. I've used your method to apply to my case but it didn't work. Here is what I write.

Measure =

var _average = calculate(AVERAGE('2023 RM Raw Data'[Amount]),REMOVEFILTERS('Month column'))

return

IF(SELECTEDVALUE('Month column'[Display Value])="Average",_average,SUM('2023 RM Raw Data'[Amount]))

Why it didn't show the average amount？Is it because my data is bit complex?

Thank you so much. If you are free, please see my own post problem: Re: Add an average column - Microsoft Fabric Community