Forum Discussion

spandy34's avatar
spandy34
Responsive Resident
4 years ago
Solved

Matrix Average Column

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 R...
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

v-chenwuz-msft's avatar
v-chenwuz-msft
Community Support
4 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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