How to group by in dax ?

[bourne2000]

Hi

I am having below table,

Project	Name	Total Days to complete
A	Jim	2
B	Jim	3
C	Jim	2
D	Carl	4
E	Carl	1
F	Carl	1
G	Carl	2

I want to group by name and count the project. Then I need to calculate the average days for each name

need output as below

Name	Number of Projects	Total Average days to complete
Jim	3	2.33
Carl	4	2

For an example of calculating the Total average days to complete,

for Jim, it has three projects and the total days to complete was 7, so the average is 7/3 = 2.33

I have more than 50 names and projects, can anyone advise how to do this in DAX?

[SanketBhagwat]

Hi @bourne2000 .

To calculate Number Of Projects using GroupBy, you can create a measure using the following DAX and then add it in your table visual;

Number Of Projects = var a =SUMMARIZE(Table1,Table1[Name],"t",COUNT(Table1[Product]))

RETURN SUMX(a,[t]).

To calculate Average Days to Complete, create a measure with the help of following DAX

Average Days to Complete = AVERAGE(Table1[Total Days to Complete])

You will get your desired result as;

 Regards,

Sanket Bhagwat.

 If this post answers your question, then do please mark it as 'Accept as Solution' so that other members could find it quickly.

[dobregon]

Check if it is possbile that you can repeat the project & name in the same table, depends on your model it could be possible. 

If it is possible you can do a distinctcount of projects, so if it is repetead a project with name it will count only one
https://docs.microsoft.com/es-es/dax/distinctcount-function-dax 

If it is not possible and your table will have unique Project + Name.. you can do a simple Count to count the number of projects per Name. Remember that in your visual you need to add always the Name in order to show the information by name

To calculate the average days, you can do a simple division

DIVIDE(SUM(Days to complete), measure count projects, BLANK())

[Jihwan_Kim · Accepted solution]

Hi, @bourne2000 

Number of projects Measure =

COUNTROWS('Table')

Total Avg days to complete measure =

SUMX ( 'Table', 'Table'[Total Days to complete] ) / [Number of projects Measure]

https://www.dropbox.com/s/wz1owmjudf3kxmq/bournev3.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM