cancel
Showing results for
Did you mean:
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.

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.

Kind regards

ICR

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Matrix subtotal excluding "0" values?

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

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

## Re: Matrix subtotal excluding "0" values?

Sample Input Data

Default Matrix BehaviourExcluded Zero values from the Matrix

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

Proud to be a Datanaut !

Regular Visitor

## Re: Matrix subtotal excluding "0" values?

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

ICR

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

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

Proud to be a Datanaut !

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

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

Kind regards.

ICR

Super User

## Re: Matrix subtotal excluding "0" values?

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

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

Proud to be a Datanaut !

Regular Visitor

## Re: Matrix subtotal excluding "0" values?

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.

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

Kind regards

ICR.

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 !

Regular Visitor

## Re: Matrix subtotal excluding "0" values?

Yes sure!, here is the sample.

I think my table was diferent than yours.

Kind regards

Community Support Team

## Re: Matrix subtotal excluding "0" values?

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

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.