cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ICRdatalover Regular Visitor
Regular Visitor

Matrix subtotal excluding "0" values?

Hi everyone, 

 

I know there are severals post with similar doubts but no one is suitable for my problem.

 

I have a Matrix-table like below wich is showing the average in the subtotals rows, what I would like to know what is the DAX formula to exclude the "0,00s" values from this subtotal-average, but not with a filter excluding the "number 5" because today number 5 doesnt have values but it will have them in a close future.

 

bad subtotal.PNG

 

 

 

 

 

 

 

 

 

 

 

 

Are there any way to do that? There are similar problems on the forum but not the same.

 

Any help will be really appreciate it.

 

Thank you in advance.

 

Kind regards

 

ICR

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Matrix subtotal excluding "0" values?

Hi @ICRdatalover ,

 

Please download a demo from the attachment. 

1. If you don't want to transform your data, you can create 7 measures like below.

Value1Measure =
CALCULATE ( AVERAGE ( 'Sample'[value1] ), 'Sample'[value1] <> 0 )

2. I would suggest you transform the data structure. 

Measure =
CALCULATE ( AVERAGE ( Table1[Value] ), Table1[Value] <> 0 )

Matrix-subtotal-excluding-0-values

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
10 REPLIES 10
Super User
Super User

Re: Matrix subtotal excluding "0" values?

@ICRdatalover  Please try as below

 

image.pngSample Input Data

image.pngDefault Matrix Behaviourimage.pngExcluded Zero values from the Matrix



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





ICRdatalover Regular Visitor
Regular Visitor

Re: Matrix subtotal excluding "0" values?

Hi @PattemManohar!

 

Thank you for taking the time to help me.

 

The problem is that its and AVERAGE, when you do the SUM like you solution it works fine, because its a "0",  but when you do it with average the total do the average with the "0" values, what Im struggling is trying to exclude this "0" from the average but showing it , not excluding it on a filter.

 

Im not sure if im explaining it properly, here is a pic of what happend with the average

 

Captura.PNG

Thank you for your help!

 

 

ICR

Super User
Super User

Re: Matrix subtotal excluding "0" values?

@ICRdatalover  Ok Gotcha !!

 

Please try this as a New Measure.

 

Test236 = 
VAR _CountRows = COUNTROWS(FILTER(Test236MatrixZeroExclusion,Test236MatrixZeroExclusion[Value]>0))
VAR _Sum = SUM(Test236MatrixZeroExclusion[Value])
VAR _TotalAvg = _Sum/_CountRows
RETURN IF(_Sum=0,0,_TotalAvg)

image.png



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





ICRdatalover Regular Visitor
Regular Visitor

Re: Matrix subtotal excluding "0" values?

Hi @PattemManohar!!

 

Now It looks exacly what I need, thanks a lot.

 

But how should I fit this measure in the table? My table just have "Rank" on rows and "Value1", "Value2", etc on values.

 

Something is wrong and there is an error like the image

12.PNG

Could you please share the .pbix ?Maybe I will understand it better. Thanks a lot Pattem!

 

Kind regards.

 

ICR

Super User
Super User

Re: Matrix subtotal excluding "0" values?

@ICRdatalover I can't see that you have mentioned any Columns field in the screenshot... 

 

image.png



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





ICRdatalover Regular Visitor
Regular Visitor

Re: Matrix subtotal excluding "0" values?

Hi @PattemManohar!

 

Yes, probably thas the issue why your solutions doesnt work for me, I didnt add any columns, just Rows and Values in the Matrix Table.

13.PNG

 

How should I change the measure to fit it in this table? 

 

Thank you in advance.

 

Kind regards

 

ICR.

Super User
Super User

Re: Matrix subtotal excluding "0" values?

@ICRdatalover  Is it your table structure is aligned with the structure of sample input data that I've posted above ? If not, could you please post the sample data 



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





ICRdatalover Regular Visitor
Regular Visitor

Re: Matrix subtotal excluding "0" values?

Yes sure!, here is the sample.

 

https://drive.google.com/open?id=1YaQvsWoE3HI4PjStnRu8V1AOMhs96gn_

 

I think my table was diferent than yours.

 

Kind regards

 

 

Community Support Team
Community Support Team

Re: Matrix subtotal excluding "0" values?

Hi @ICRdatalover ,

 

Please download a demo from the attachment. 

1. If you don't want to transform your data, you can create 7 measures like below.

Value1Measure =
CALCULATE ( AVERAGE ( 'Sample'[value1] ), 'Sample'[value1] <> 0 )

2. I would suggest you transform the data structure. 

Measure =
CALCULATE ( AVERAGE ( Table1[Value] ), Table1[Value] <> 0 )

Matrix-subtotal-excluding-0-values

 

Best Regards,

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