Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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]) )
@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!
Proud to be a Super User!
Paul on Linkedin.
@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!
Proud to be a Super User!
Paul on Linkedin.
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
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]) )
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
83 | |
76 | |
65 |
User | Count |
---|---|
120 | |
111 | |
94 | |
83 | |
77 |