Matrix subtotal excluding "0" values?

[ICRdatalover]

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.

Thank you in advance.

Kind regards

ICR

[ICRdatalover]

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

Thank you for your help!

ICR

[PattemManohar]

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

[ICRdatalover]

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

[PattemManohar]

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

[ICRdatalover]

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.

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

Thank you in advance.

Kind regards

ICR.

[PattemManohar]

@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 

[ICRdatalover]

Yes sure!, here is the sample.

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

I think my table was diferent than yours.

Kind regards

[v-jiascu-msft · Accepted solution]

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 )

Best Regards,

[ICRdatalover]

Hello!

That is exacly what I needed, problem solved! Also I will change the structure as well to use the second way.

Thanks a lot for taking the time to help me @v-jiascu-msft and @PattemManohar!!

Kind regards.

ICR:

[PattemManohar]

@ICRdatalover  Please try as below

Sample Input Data

Default Matrix BehaviourExcluded Zero values from the Matrix