Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Average Grand total instead of Sum in Matrix

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! 🙂

1 ACCEPTED SOLUTION
Solution Sage

@alicia_mtz 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))`
13 REPLIES 13
New Member

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 @alicia_mtz @ddpl @Ashish_Mathur

Super User

Hi,

Share the download link of your PBI file.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Community Support

Hi @alicia_mtz ，

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

Helper II

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!

Solution Sage

@alicia_mtz Have you try my below reply??

Community Support

Hi @alicia_mtz ,

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

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Helper II

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!! 🙂

Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Solution Sage

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])returnIF(HASONEVALUE('Table'[Company]), SUM('Table'[Sales]), AVERAGEX(FILTER('Table', _avg>0), _avg))`

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Solution Sage

@alicia_mtz 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))`
Helper II

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!! 🙂

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors