Average a Measure

[Anonymous]

Hi,

Department	Name	Days	365
finance	Jimmy	5	1%
IT	John	2	1%
IT	Greg	3	1%
Legal	Nick	5	1%
Legal	Tom	1	0%

I had a pbi file like above. Here are the descriptions for each column.

Department: It is conditional column based on each employee's business unit number.

Employee: Straight from the excel file

Days: Used a measure Distinctivecount to count the days based on a period of transactional data.

Days/365: Days/365

Now I would like to know the average time spent on employees based on business unit, the result is like below.

Department	Average Days	365
finance	5	1%
IT	2.5	1%
Legal	3	1%

Just wonder how to get this "average days", what measure I should use?

Thank you!

[az38 · Accepted solution]

Hi @Anonymous 

ifyour dat field is a measure, it looks like you should use AVERAGEX() function https://docs.microsoft.com/en-us/dax/averagex-function-dax 

like

Average Days = CALCULATE(AVERAGEX(Table,[Days]), ALLEXCEPT(Table, Table[Department]) )

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous · Accepted solution]

Sorry I am a bit confused with the"Table" in the measure - could you please explain what it means?

Average Days = CALCULATE(AVERAGEX(Table,[Days]), ALLEXCEPT(Table, Table[Department]) )

[az38 · Accepted solution]

@Anonymous 

it is the data source name which contains measure {Data] and field column [Department]

do not hesitate to give a kudo to useful posts and mark solutions as solution

[PaulDBrown · Accepted solution]

@Anonymous 

Assuming the following:

- The column "Department" is in a table named Data Table (substitute for your table name obviously)

- Days is a measure: in my case I've called it [Days Measure]

Use this measure to calculate the average days:

Average Days by Department = AVERAGEX('Data Table'; [Days Measure])

And this measure to calculate % over year:

% Average Days / 365 = DIVIDE([Average Days by Department]; 365)

Now set up a table/matrix using the Department column to get you this:

 Hope this helps!

[az38 · Accepted solution]

Hi @Anonymous 

ifyour dat field is a measure, it looks like you should use AVERAGEX() function https://docs.microsoft.com/en-us/dax/averagex-function-dax 

like

Average Days = CALCULATE(AVERAGEX(Table,[Days]), ALLEXCEPT(Table, Table[Department]) )

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous · Accepted solution]

Sorry I am a bit confused with the"Table" in the measure - could you please explain what it means?

Average Days = CALCULATE(AVERAGEX(Table,[Days]), ALLEXCEPT(Table, Table[Department]) )

[az38 · Accepted solution]

@Anonymous 

it is the data source name which contains measure {Data] and field column [Department]

do not hesitate to give a kudo to useful posts and mark solutions as solution