Average Grand total instead of Sum in Matrix

[Anonymous]

Hi community!

I have a matrix and I want to show the Average instead of the Sum in the grand total. I've read other posts here but none of them have the solution I need (or I didn't find it).

On the left of my pic you can see the normal Matrix with the values (dummy data) and the sum at the end. On the right side is the option when you right click the values and change Sum for Average. What I want is that Total results in the right side but on the left side.

So in summary I need the sum of the values for each row but the average at the end. Also on the right pic is calculating the average counting the zeros as value, but when there is no value (or value zero) I want it not to be counted, so for this example the result on that column should be 38,33 and not 28,75.

I cannot find any option that does what I need and I'm still new using power bi, so I don't know if it would be possible to create a measure in DAX for something like this?

Thank you very much in advance! 🙂

[hacksverstappen]

Hi All, along these lines, does any one happen to have a solution to finding the average of the summed total amounts? for this data set the correct answer would be below where the total takes the averages of the summed values @v-mengzhu-msft @Anonymous @ddpl @Ashish_Mathur 

[Ashish_Mathur]

Hi,

Share the download link of your PBI file.

[v-mengzhu-msft]

Hi @Anonymous ，

Is this result meet your needs?

For the first picture, I calculate the sum of each row and their avg through two measure:

Sum = sumx('Table','Table'[Shoes]+'Table'[Trousers]+'Table'[Tshirts])

avg = var _table=SUMMARIZE('Table','Table'[company],'Table'[Shoes],'Table'[Trousers],'Table'[Tshirts],"sum",[Sum])
var _avg1='Table'[Sum]/3
var _avg2='Table'[Sum]/2
return if(SELECTEDVALUE('Table'[company])="Deca",_avg2,_avg1)

For the third picutre, as you want, I get the average of every company through three measure:

average of shoes = var _count=CALCULATE(COUNT('Table (2)'[avg of shoes]),FILTER('Table (2)','Table (2)'[avg of shoes]<>0))
return if(HASONEVALUE('Table (2)'[company]),SELECTEDVALUE('Table (2)'[avg of shoes]),SUM('Table (2)'[avg of shoes])/_count)

The other two are written in a similar way, the details of which you can understand through my PBIX file.

Best regards,

Community Support Team Selina zhu

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

[Anonymous]

Hi @v-mengzhu-msft and @Ashish_Mathur 

Thank you so much for trying! I really appreaciate it!

So this is the result I'm looking for:

@Ashish_Mathur  when I drag your measure I have all averaged, but not the sum in the rows and the average in the total. Like this:

Thank you!

[ddpl]

@Anonymous Have you try my below reply??

[v-mengzhu-msft]

Hi @Anonymous ,

This is what I get:

You can check my pbix file for understanding.

Best regards,

Community Support Team Selina zhu

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

[Ashish_Mathur]

Hi,

Try these measure

Average sales = average(Data[Sales])

Avg = if(hasonevalue(Companies[company]),[average sales],averagex(filter(values(Companies[Company]),[average sales]>0),[average sales])

Drag the Avg measure to the visual.  I have assumed that Companies is a Table with a column in there called Company and there is a relationship (Many to One and Single) from the Company column of the Data Table to the Company column of the Companies table.

Hope this helps.

[Anonymous]

Hi @Ashish_Mathur 

First of all million thanks for your reply! Sorry I couldn't test your code until now.

I've tried it but this is the result I'm getting. Am I doing it correctly or I'm dragging the measure to the wrong place?

I'm seeing both columns, sum and avg, but not the avg as a grand total in the sum column.

I do see that the avg with zero values is correctly calculated now, that's a step further!

Do you know what I'm doing wrong? thank you!! 🙂

[Ashish_Mathur]

You are welcome.  Drag only the avg measure (which i suggested in my post above) to your visual.

[ddpl]

@Ashish_Mathur ,

I tried to use variable inside measure, but it didn't work. But as you suggested earlier a separate measure worked perfectly fine.

Can you explaine why? It will help us alot to understand DAX.

Your Solution (working fine)...

Average = AVERAGE('Table'[Sales])

Avg = IF(HASONEVALUE('Table'[Company]), SUM('Table'[Sales]), AVERAGEX(FILTER('Table', [Average]>0), [Average]))

My Try (not working)...

Avg_tot =

var _avg = AVERAGE('Table'[Sales])

return
IF(HASONEVALUE('Table'[Company]), SUM('Table'[Sales]), AVERAGEX(FILTER('Table', _avg>0), _avg))

[Ashish_Mathur]

That wasn't my formula.  I used a VALUES() function within the FILTER() function.  See carefully.  Furthermore, i do not use variables so i may not be able to help you with your question.

If my previous reply helped, please mark that reply as Answer.

[ddpl · Accepted solution]

@Anonymous please use this

Measure = 
var _count = COUNTX(FILTER('Table', 'Table'[Sales] <> BLANK()),'Table'[Company])
return
        IF(HASONEVALUE('Table'[Company]),SUM('Table'[Sales]),DIVIDE(SUM('Table'[Sales]),_count))

[Anonymous]

Hi Team,

I also need the same. But like as of now we are calculating the count only for company column, but I have other dimensions which I need to add in rows in Matrix visual  or may be I need to shift the dimensions from row to column in matrix visual. Could you tell me the alternative solutions where we can add mutiple dimensions and accordingly my totals at every level gives me the average in each totals rows.

[Anonymous]

My apologies @ddpl !! I missed this!

It works!! This is exactly what I needed! This is my result now:

Thank you all for all your help!! I'm still learning and it's wonderful to have people willing to help!

Million thanks to all!! 🙂