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
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/

       

      • powerbienthuzi's avatar
        powerbienthuzi
        Frequent Visitor
        4 years ago

        That worked. Thank you!