topic Re: Sum the count of rows to get an average in DAX Commands and Tips

Sum the count of rows to get an average

Shane86 — Thu, 06 Feb 2020 15:16:49 GMT

This is more of a DAX/Power Pivot question but this seems like the best place for this question.

Im basically trying to replace the "grand total" with the the average.

Each column is a given week. the rows include the count of a certain text within my data set. i cant figure out how to get the average of the weeks with Dax

ive tried

measure = sum(count("certain text")) but i get an error saying the sum measure cant use count within it.

Re: Sum the count of rows to get an average

Anonymous — Thu, 06 Feb 2020 15:31:55 GMT

if you want the structure in same way then

Create total column as

Total = 'Table 4'[Column1]+'Table 4'[Column2]

You can add all six columns here

Total average per Name =

AVERAGEX(

KEEPFILTERS(VALUES('Table 4'[Name])),

CALCULATE(SUM('Table 4'[Total]))/2

)

In my case i used 2 columns you can replace two with 6 in formula.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

Re: Sum the count of rows to get an average

Shane86 — Thu, 06 Feb 2020 15:40:02 GMT

Thats a good solution, my main issue is the weeks are going to be growing every week, its not going to always be just 6 weeks

With your method will i have to go in and update the measure every time a new weeks worth of data is added?

Also, will the average be dynamic if i were to use a slicer and look at weeks 2-4 or weeks 1-7?

Re: Sum the count of rows to get an average

Anonymous — Thu, 06 Feb 2020 16:10:26 GMT

in that case you need to pivot your table and take column in row and count the number of values per name.

So that it will be dynamic and you do not need to do manual changes everytime.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

Re: Sum the count of rows to get an average

Shane86 — Thu, 06 Feb 2020 16:15:22 GMT

Understood, i just dont know how to do that. is that with a measure? or strictly just within the pivot? ive tried to do get the avearge within the pivot but it averages all the columns

thanks again for all your help with this!

Re: Sum the count of rows to get an average

Anonymous — Thu, 06 Feb 2020 17:51:33 GMT

https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-Unpivot/td-p/574832

Check this article or simple unpivot your column to attribute value pair.

Then your table will be like
Name attribute value
Attribute contain all column row and row and value contains corresponding value.

Then you to do is

New measure=Calculate (divide(sum(table[attribute]),count(table[attribute])),allexcept(table,table[name]))

If it resolves your problem mark it as a solution and give Kudos.