Forum Discussion

ICRdatalover's avatar
ICRdatalover
Icon for Advocate I rankAdvocate I
7 years ago
Solved

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.

 

Thank you in advance.

 

Kind regards

 

ICR

Need Help
Tutorial Requests
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    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,

10 Replies

  • ICRdatalover's avatar
    ICRdatalover
    Icon for Advocate I rankAdvocate I
    7 years ago

    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's avatar
      PattemManohar
      Icon for Community Champion rankCommunity Champion
      7 years ago

      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's avatar
        ICRdatalover
        Icon for Advocate I rankAdvocate I
        7 years ago

        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