topic Re: How to get average of countrows in a table as total in DAX Commands and Tips

How to get average of countrows in a table as total

Anonymous — Wed, 03 Aug 2022 14:32:09 GMT

Hi guys,

After being a reader of this community for weeks, I would need some help for this unanswered question.

It's quite simple, but I couldn't find the solution. I would like to get the average as the total of the table of a measure which counts the number of rows.

Here is my data sample

I would like the average next to the red arrow.

My measure are

nb_days_active =

AVERAGEX(

'blabla,

CALCULATE(Countrows('blabla'))

)

nb_days_stock =

CALCULATE(

AVERAGEX('blabla',COUNTROWS('blabla')),

'blabla'[quantity_stock_internal]>0

)

Thanks in advance 🙂

Re: How to get average of countrows in a table as total

amitchandak — Wed, 03 Aug 2022 15:12:30 GMT

Anonymous , This average need a level/column/group by. The one used in visual '

nb_days_active =

AVERAGEX(

values('blabla'[Column]) ,

CALCULATE(Countrows('blabla'))

)

nb_days_active =

AVERAGEX(

summarize('blabla','blabla'[Column],'blabla'[Column2]) ,

CALCULATE(Countrows('blabla'))

)

Re: How to get average of countrows in a table as total

Anonymous — Thu, 04 Aug 2022 09:23:52 GMT

Thanks amitchandak for your fast reply. I tried both, but still, the total is a sum. 😞

What am I doing wrong?

nb_days_active =

//if a product is active, there is a row in the table every day, if the product has got stock

nb_days_active =

AVERAGEX(

SUMMARIZE('blabla','blabla'[id],'blabla'[snapshot_date]),

Countrows('blabla')
)

nb_days_active =

AVERAGEX(

SUMMARIZE('blabla','blabla'[id]),

Countrows('blabla')
)

The first row of my table is the ID (of the product), it still doesn't average the total.

Re: How to get average of countrows in a table as total

Anonymous — Fri, 05 Aug 2022 10:02:22 GMT

I found the solution. I needed to add the countrows within my summarize

AVERAGEX(

SUMMARIZE('blabla','blabla'[id],"active_days",COUNTROWS('blabla')),

[active_days]

)

Re: How to get average of countrows in a table as total

v-yadongf-msft — Mon, 08 Aug 2022 01:54:01 GMT

I am so glad to hear that your problem has been solved . Please consider Accept it as the solution to help the other members find it more quickly.