I need a DAX measure to calculate an average of scores by selected agents over selected months

[ropebender]

I have been trying to use the following measure to create a column in my Visual Table to give me the average of each agent for a group of selected months.  If I change the slicer for different months it gives me the average for each month instead of averaging them together.  Can you help?:

Average Agent Score for Months =

CALCULATE(

    average(TestScores[Score])

    ,FILTER(ALL(TestScores),

             TestScores[Agent] IN VALUES(TestScores[Agent]))

    ,FILTER(ALL(TestScores),

        TestScores[Month] IN VALUES(TestScores[Month]))

)

Here is what I get:

Example: For Barney, I expect to see the average of 39 ((39+48+34)/3) in all three places.  and if I change the months, I expect to see the average recalculated for the new group of months.

I am using a test table of data:

Month	Agent	Score	Team
JAN	Bill	32	Barney
FEB	Bill	32	Barney
MAR	Bill	37	Barney
APR	Bill	33	Barney
MAY	Bill	48	Barney
JUN	Bill	40	Barney
JUL	Bill	37	Barney
AUG	Bill	45	Barney
JAN	Mary	43	Barney
FEB	Mary	32	Barney
MAR	Mary	50	Barney
APR	Mary	43	Barney
MAY	Mary	46	Barney
JUN	Mary	50	Barney
JUL	Mary	46	Barney
AUG	Mary	50	Barney
JAN	Raval	46	Mavis
FEB	Raval	41	Mavis
MAR	Raval	43	Mavis
APR	Raval	43	Mavis
MAY	Raval	37	Mavis
JUN	Raval	39	Mavis
JUL	Raval	35	Mavis
AUG	Raval	30	Mavis
JAN	Luego	30	Mavis
FEB	Luego	33	Mavis
MAR	Luego	33	Mavis
APR	Luego	50	Mavis
MAY	Luego	41	Mavis
JUN	Luego	34	Mavis
JUL	Luego	36	Mavis
AUG	Luego	38	Mavis

[ropebender]

I found my issue, but I don't know what to do about it.

I removed all the columns in the visual except Month, EID, and Adj_Rank_Indes and used the suggested measure:

---------------------

Avg Adjusted Rank Index =

IF(ISINSCOPE(MonthlyRanking_ConsolidatedRank[EID]),

    averagex(

        ALLSELECTED(MonthlyRanking_ConsolidatedRank[Month]),

        CALCULATE(SUM(MonthlyRanking_ConsolidatedRank[Adj_Rank_Index]))

    ),

    BLANK()

)

--------------------------

It works great!  

So now I started adding in other needed columns to the visual, and I found that when value changed in any of the new columns, the average was broken out separately for that changed value:

This happened when any of the values in the new columns changed.  What I need is a way for me to add other columns to the visual, or change any slicers other than Month or EID and have the average be that of all the months for that EID.

Is this possible:  do  need to create a new table (or virtual table) with just those three fields, do the average, and use RELATED() to pull the average from the new table?  

[xifeng_L · Accepted solution]

Hi @ropebender 

You can try below measure.

Average Score = 
IF(ISINSCOPE('Table'[Agent]),
    AVERAGEX(
        ALLSELECTED('Table'[Month]),
        CALCULATE(SUM('Table'[Score]))
    ),
    BLANK() // Return blank at Total Row
)

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[ropebender]

This worked great 🙂  But I would love to know why.  To my beginner's eyes it makes no sense.

In any case, Thank you for the solution I was looking for.

Van!!

[Gabry]

Hello @ropebender ,

I don't understand, do you want to calculate the average per team or per agent? Because you said For Barney I expect, isn't Barney a team?

I think this is the measure you are looking for:

Average Measure Team = CALCULATE(AVERAGE('Table'[Score]), ALLSELECTED('Table'[Month],'Table'[Agent]))

Average Measure Agent= CALCULATE(AVERAGE('Table'[Score]), ALLSELECTED('Table'[Month]))

Let me know if it's ok 😉

[ropebender]

Thanks, Gabry;

I gave that one a try, but it still gives me the individual scores back, not the average.  Xifeng_L's version worked OK on my sample data, gave me just what I needed.  But when I tried to translate it to the real data instead of the sample data it didn't work.  The real data is a larget table with lots more fields, the agents have an ID Number instead of a name, and the Scores are decimal numbers between 1 and zero.  I didn't think that would matter when I translated the formula but it does.

Below is what I get from the real data

And the measure as I re-wrote it deals with only one large table:

Avg Adjusted Rank Index =

IF(ISINSCOPE(MyTable[EID]),

    averagex(

        ALLSELECTED(MyTable[Month]),

        CALCULATE(SUM(MyTable[Adj_Rank_Index]))

    ),

    BLANK()

)

Worked beautifully in the sample data but when  put it into the real data I just get back the Adj_Rank_Index for each month, not the average of the three selected months.  I just showed two agents, but it's the same for all 200 or so.

Any suggestions folks?

[xifeng_L]

Do you have a sort by column function applied to the month field? If yes, you need to add the sorting column to ALLSELECTED, such as : ALLSELECTED('Table'[Month], 'Table'[Month Index])

If not use the sort by column function, you can provide some sample data with the same table structur as your real data.

[ropebender]

Thanks for the idea.  I doublechecked the data, and I am not using a Sort by Column in the table.  THe table is imported just as it exists in SQL.  I can't duplicate internal information, but here is what I can show you from the imported table:

Month and EID are Text fields and Adj_Rank_Index is Decimal data .  I do have several slicers on the canvas, all the values in the slicers come directly from fields in the main table, no refrences or joins to other tables are used.

and I have selected ALL for everything but the Month field to limit the data to the ones in the sample.

Still looking 🙂  I appreciate your continued suggestions 🙂

Van!!