Sum the count of rows to get an average

[Shane86]

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.

[Anonymous]

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.

[Anonymous]

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.

[Shane86]

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!

[Anonymous · Accepted solution]

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.

[Shane86]

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?