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