How to Calculate Average of CountRows per Manager and Display on All Rows

[andrelopes2711]

Good afternoon everyone, I need help solving this problem. In the first column of the print, I created a measure called countrows. Based on this, I need to create a new measure with the average of these countrows per manager and return this average for all rows, not just the total row. Later, I'll create a lighthouse where I need to use this average. Also, here's the measurement I tried to create for this average, but it didn't work.

average_teste = 

CALCULATE(

AVERAGEX(

FILTER(

VALUES(Fat_ate_cambios_Estados_envio[USER_TEAM_LEADER_LDAP]),

NOT ISBLANK([#Casos_cambios_estados_envio])

&& [#Casos_cambios_estados_envio] <> 0

),

[#Casos_cambios_estados_envio]

),

KEEPFILTERS(

ALLEXCEPT(

Fat_ate_cambios_Estados_envio,

Fat_ate_cambios_Estados_envio[USER_TEAM_NAME]

)

)

)

[Praful_Potphode]

Hi @andrelopes2711 ,

Please try below measure:

average_teste = IF(
    ISINSCOPE(Fat_ate_cambios_Estados_envio[USER_TEAM_LEADER_LDAP]),
   
AVERAGEX(
    Fat_ate_cambios_Estados_envio,
    [#Casos_cambios_estados_envio]
)
,BLANK()
)

above measure will show blank if the total row is selected and it will show value when the USER_TEAM_LEADER_LDAP is selected.this is used a lot for matrix visuals.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[v-dineshya]

Hi @andrelopes2711 ,

Thank you for reaching out to the Microsoft Community Forum.

As you mentioned your previous post, you need to create a new measure with the average of  countrows per manager and return this average for all rows, not just the total row. Please refer below sample data snap.

Please refer below DAX measure, ouput snap and PBIX file.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[andrelopes2711]

Good afternoon @v-dineshya and @Praful_Potphode , I included the suggestion but it is returning the average even for cases where there are no values ​​in [#Casos_cambios_estados_envio] and the average is also not correct, taking the example of BR_MED_PNRME_Offline and for 09/01/2025 the values ​​are 12, 9, 7, 4, 2, 2 and the average of this should be 6 but it is returning 2, I changed the measurement instead of ALL to AllSelected but it did not work. =/

Attached is the pbix I am using and the relationships used.

https://drive.google.com/file/d/1ESH1_1POO7fqY0Ys6I-MiVBGfXT0QB-M/view?usp=sharing 

[v-dineshya · Accepted solution]

Hi @andrelopes2711 ,

Thank you for the update. I have replicated from my side. It is working from my side. Please check from your side like any filters or any other need to change? Please refer below output snap and attached PBIX file.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @andrelopes2711 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[PhilipTreacy]

Hi @andrelopes2711 

Please supply your PBIX file or some sample data we can work with.

Regards

Phil