cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Average instead of Total in Row & Column in Matrix Visual

I have created the following 2 measures:

 

Case 1:

Average = 

VAR AVERAGE_ = AVERAGEX(all(Data_shipments[Country]) ,[Success Rate new])
RETURN
( IF ( HASONEVALUE(Data_shipments[Country]), [Success Rate new], AVERAGE_ ) )

 

I have "Country Name"  in the row &  "Product name" in the column. Using the above measure, I get the correct value for the average for "Success Rate" in the column total, but not at row level. 

 

Case 2:

Average  = 

VAR AVERAGE_ = AVERAGEX(all(Data_shipments[Product Name]) ,[Success Rate new])
RETURN
( IF ( HASONEVALUE(Data_shipments[Product Name]), [Success Rate new], AVERAGE_ ) )

 

Using the above measure, I get the correct value for the average for "Success Rate" in the row total, but not at the column level. 

 

What modification did I need to do to get average of success rate at both column and row total? Please find attached screenshot for both cases.

Please Guide

Thanks in advance!

 

Wrong AVG  in column totalWrong AVG in column totalWrong AVG in row totalWrong AVG in row total

7 REPLIES 7
Dnerada
Post Patron
Post Patron

the issue looks like it is averaging all including blanks 

 

try this 

 

if(not(isblank([Success Rate new])),
averagex(filter(allselected(Data_shipments[Country]),

not(isblank([Success Rate new]))),

[Success Rate new]))

@Dnerada Thanks for your reply ,I am attaching screenshot for the reference ,will you please let me know what measure I need to right to calculate average % ,as % accuracy and % condition is coming from other measures.Pls have a lookPls have a look 

Is there a chance you could send a sample of the data.  Sometimes it's hard to help with out data to better understand how to help. 

Hi ,

 

In matrix table it is not possible to have a average % in column subtotals ,row subtotal is working fine .Still you can workaround like make a seperate measures of % for each product and then make another measure of average of all products.But this meathod is static not dynamic .It worked in my case ,as products are static in my case .

 

Hope it helps..

Drop a comment if it helped..

Cheers..

Bhagyashrim_242
Frequent Visitor

@amitchandak  , @Greg_Deckler ,@mahoneypat  will you please advice what I need to do in such case ,as table values are coming perfect but row total and column total is not catching average percentage ,your help will be appreciated.Thanks in advance

Bhagyashrim_242
Frequent Visitor

Hi , I have also came across with the same condition ,have you solved it?

v-cherch-msft
Microsoft
Microsoft

Hi @Anonymous

 

You may have a look at below post and check if it helps. It's better if you could share some data which could reproduce your scenario and your desired output.

https://community.powerbi.com/t5/Desktop/AVG-instead-Total-in-matrix/td-p/327031

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors