Forum Discussion

powerbienthuzi's avatar
powerbienthuzi
Frequent Visitor
4 years ago
Solved

Simple GroupBy Question

Hello -   I am trying to group people with the same period to show the summed value of compensation. So for instance, this employee example should show  14505 in period one. My current approach use...
Need Help
AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

OK. What are you currently getting and what are you trying to get your visual to look like?

 

You shouldn't need any fancy DAX to do basic aggregations. Just drag the column you want to group on and the column you want to sum into your visual. (You may need to tell it not to aggregate pperiod.)

powerbienthuzi's avatar
powerbienthuzi
Frequent Visitor
4 years ago

I see. I have deleted my code for groupby and am trying to get it in a table format using Summary now

 

Short PnL = SUMMARIZE(PnL_Data,
PnL_Data[deID],
PnL_Data[pstartdate],
PnL_Data[pperiod],
"total compensation", SUM(PnL_Data[AccruedCompensation])
)
 
but the aggregation is not adding up right so I'm sure I made a mistake.
 
This is the picture of the result I'm getting:
 

 

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    Does your PnL table have any filters you want that didn't get included when you summarized it?

    • powerbienthuzi's avatar
      powerbienthuzi
      Frequent Visitor
      4 years ago

      Hi Alexis - It has no filters that I needed. 

      • smpa01's avatar
        smpa01
        Community Champion
        4 years ago

        powerbienthuzi  looks like you need a derivedTbl, Can you try this

         

        derivedTbl=
groupby(PnL_Data,
PnL_Data[deID],
PnL_Data[pstartdate],
PnL_Data[pperiod],"sum", SUMX(CURRENTGROUP(),PnL_Data[AccruedCompensation]))

         

        SUMAMRIZE has more layers than it looks

        https://www.sqlbi.com/articles/all-the-secrets-of-summarize/