Adding both a total and an average column

[abdul26]

I have created a marix with the following details 

Rows:  Category types 

Columns:   Week numbers 

Values - Values 

I wish to add both an average and a total column.

I am able to generate them individually but not together.

PowerBi provides the option of column subtotal that way I can have a total column. 

By using the following measure = 

IF( HASONEVALUE( week ) , [val_measure]  , AVERAGEX( VALUES( week ) ,  [val_measure]  ))     ,

I can display the average column in place of the total column.

How can I display both of them simultaneously.

[abdul26]

@mangaus1111  wow , thank you so much for your response.

It's a really nice measure !! 

[v-xiaosun-msft]

Hi @abdul26 ,

According to your description, here is my solution.

I created a sample.

Create a measure to calculate the average value.

Average = AVERAGE(Tabelle1[Value])

Make sure the option followings is turned on. And then put the measure and "value" to values together. Put "value" to the first which will calculate the total.

Note that when we put measure to "value", it will create columns under  "columns" as the screenshot shows.

And you can refer to the following link.

Create a matrix visual in Power BI - Power BI | Microsoft Learn

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

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

[mangaus1111]

sorry, but  I don´t know any formal method

[mangaus1111]

Hi @abdul26 ,

you can use 2 matrixs, one above the other.

See may pbi sample file here:

https://1drv.ms/u/s!Aj45jbu0mDVJiHXjhUleoc6Dg-eZ?e=SgOKxc

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

[abdul26]

Thank you for your reply @mangaus1111 , it looks like a way around.

Isn't there any formal method to do it ? 

[mangaus1111 · Accepted solution]

Hi @abdul26 ,

in this pbi file there is a very formal method for your problem. Let me know if the link does not work.

https://1drv.ms/u/s!Aj45jbu0mDVJiH9FTsdOyUHsl_Or?e=ErSRsr

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