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

Reply
alicia_mtz
Helper II
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.

 

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

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

View solution in original post

13 REPLIES 13
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 @alicia_mtz @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 @alicia_mtz ,

 

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

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!

@alicia_mtz Have you try my below reply??

 

ddpl_0-1661939213393.png

 

Hi @alicia_mtz ,

 

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/

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

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

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
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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