Display the quarterly average on a Matrix

[brunohelder]

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.

[Anonymous]

Hi @brunohelder ,

If you need to show the quarterly average, you can change sum to average. if you need to add a column, you can check the following results:

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]

Thank you so much for the reply.

I could not get it to work.

Your example has a table with date and the number amount for the date.

In my source table each entry is a line with a date and the quantity is 1 (one observation). The screenshot that I have sent is a count of the lines by date (Date Hierarchy). If I do an Average I get 1.

[Anonymous]

Hi @brunohelder ,

Can you provide sample data, so that we can better solve the problem for you.

Best regards,
Community Support Team_ Scott Chang

[brunohelder]

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

[Anonymous]

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]

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]

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]

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.

[Anonymous]

Hi @brunohelder ,

Change ALL to ALLSELECTED to try it out

Best regards,
Community Support Team_ Scott Chang