Forum Discussion

Milozebre's avatar
Milozebre
Icon for Helper V rankHelper V
8 years ago
Solved

Sum by group

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

UserManagerReal Working hoursTheorical Working hours
Paul SimonJoe Santana8780
Eric ClaptonJoe Santana7480
Freddy MercuryJoe cocker6980
Elton JohnJoe cocker5480
Bruce SpringsteenPrince8480
AdelePrince6380
SadePrince2180

 

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

 

ManagerNumber of usersReal working hoursTheorical Working hours
Joe santana2161160
Joe cocker2123160
Prince3168240

 

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

Need Help
  • Milozebre's avatar
    Milozebre
    8 years ago

    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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello all,

     

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

     

    Thank you

     

    George

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    8 years ago

    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's avatar
      Milozebre
      Icon for Helper V rankHelper V
      8 years ago

      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's avatar
        TomMartens
        Icon for Super User rankSuper User
        8 years ago

        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