Forum Discussion
Average a Measure
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!
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
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
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!
4 Replies
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
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
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!