Sum by group

[Milozebre]

Hello Community, 

I have a question about sum and group by. 

I have read some post but solution dont works with my exemple. 

I have a table with this datas

User	Manager	Real Working hours	Theorical Working hours
Paul Simon	Joe Santana	87	80
Eric Clapton	Joe Santana	74	80
Freddy Mercury	Joe cocker	69	80
Elton John	Joe cocker	54	80
Bruce Springsteen	Prince	84	80
Adele	Prince	63	80
Sade	Prince	21	80

And i want some measures of column  who can show me  :

Manager	Number of users	Real working hours	Theorical Working hours
Joe santana	2	161	160
Joe cocker	2	123	160
Prince	3	168	240

I know for a lot of you it's easy but i can"t find. 

I can do with filters in PBI and visualisation but it's not easy because i make a measure for 1 manager.

Thank you a lot in advance for your help.

Dimitri

[Anonymous]

Hello all,

Will you be able to share the DAX behind the sum by group measure for this example?

Thank you

George

[TomMartens]

Hey,

I'm wondering if a "simple" table visual is sufficient for your use case, but what you are looking for can be achieved easily (as far as I understand your requirement):

Here is a little screenshot:

You just have to change the aggregation function to count for the User column.

Maybe this is already what you are looking for, if this is not the case, please describe why you need a measure in more detail.

Regards,

Tom

[Milozebre]

@Anonymous

Thank you for your response. 

It help me, i have also find this solution but why I need measure or column.

To know the values of the column theorical working days, I need the number of user. 

Total Theorical working hours = number of user * 25200

To know the account of users per manager 

Then with measure or column, i dont know what's the best way, I can calculate the total of the "real working hours "

The finality of this, it's not the table visualisation but to show me, in différent team, if the guys encode time or not and what's the attendees. 

[TomMartens]

Hey,

understand ...

So, basically it's always a good idea to create a measure instead of a calculated column, the advantages are

• smaller memory footprint (this becomes more apparent with larger datasets)
• interact with selection made by the user (e.g. slicer)

there is one disadvantage of a measure

• it can't be used as a slicer

Here is a simple example for a measure that counts the user

User cnt = 
DISTINCTCOUNT(
    'Table1'[User]
) 

If you want to start learning DAX my current recommendation is the book "Beginning DAX" by @Phil_Seamark

Maybe this gets you started.

Regards,

Tom

[Milozebre · Accepted solution]

@TomMartensTom.

I have already this measure 🙂 but its not what i want.

I found another solution with filter and Username.

Thank you for the reference also. 

Have a nice day. 

Dimitri