Forum Discussion

brunohelder's avatar
brunohelder
Frequent Visitor
2 years ago

Display the quarterly average on a Matrix

I have a matrix that is counting the rows of a table by month of activity. I would like to add to the right the quarterly average of the activity.  
Need Help
brunohelder's avatar
brunohelder
Frequent Visitor
2 years ago

I am not able to attach files... Please find an example

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi brunohelder ,

 

I made simple samples and you can check the results below:

 

AVG Count = var _t = ADDCOLUMNS('Table',"A",COUNTAX(FILTER(ALL('Table'),[Date].[Quarter]=EARLIER([Date].[Quarter])),[ID]))
RETURN DIVIDE(AVERAGEX(_t,[A]),3)

 

An attachment for your reference. Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

  • brunohelder's avatar
    brunohelder
    Frequent Visitor
    2 years ago

    On my data the average on the months it is not the same as in the sample.

    I don't understand why.

     

    I had to change a bit the formula because you where filtering ALL the table and I don't want that.

     

    Average activity per Quarter (IN) =
    VAR _t =
        ADDCOLUMNS (
            Table,
            "A",
                COUNTAX (
                    FILTER (
                        ( Table),
                       Table[ Date [.[Quarter]
                            = EARLIER ( Table[ Date].[Quarter] )
                    ),
                    Table[ID]
                )
        )
    RETURN
        DIVIDE ( AVERAGEX ( _t, [A] ), 3 )

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi brunohelder ,

       

      Because in my expression I fixed that there are three months in a quarter, even though there is no value.

       

      Hope it helps!

       

      Best regards,
      Community Support Team_ Scott Chang

       

      If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

      • brunohelder's avatar
        brunohelder
        Frequent Visitor
        2 years ago

        It seems to be related to the "FILTER (ALL" that you have.

        If I remove the ALL from your formula it changes:

        But if I Keep the Filter ALL on my formula it disregards others filters that I have and that I need.