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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

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.

 

Question - Copy.JPG

 

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
ddpl
Solution Sage
Solution Sage

@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))

View solution in original post

14 REPLIES 14
hacksverstappen
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 @Anonymous @ddpl @Ashish_Mathur 

hacksverstappen_0-1666110991843.png

 

Hi,

Share the download link of your PBI file.


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

Hi @Anonymous ,

 

Is this result meet your needs?

vmengzhumsft_0-1661924177705.png

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
Not applicable

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:

 

Question2.JPG

 

@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:

 

avg.JPG

 

Thank you!

@Anonymous Have you try my below reply??

 

ddpl_0-1661939213393.png

 

Hi @Anonymous ,

 

This is what I get:

vmengzhumsft_0-1661936732483.png

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
Super User
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/
Anonymous
Not applicable

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!

 

Measure_result.JPG

 

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

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/

@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))

 

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/
ddpl
Solution Sage
Solution Sage

@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
Not applicable

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
Not applicable

My apologies @ddpl !! I missed this!

 

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

 

solution.JPG

 

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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