Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
powerbienthuzi
Frequent Visitor

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 uses an if function but I know groupby would be the better route so any help would be appreciated.

 

groupby.PNG

1 ACCEPTED SOLUTION

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

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

8 REPLIES 8
AlexisOlson
Super User
Super User

What is the expected result? Are you trying to do this in the query editor or as a report visual or something else?

Hi Alexis - This would be a report visual andI rather have as it's own table since I want to add other columns to it. 

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.)

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:
 
error.PNG

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

Hi Alexis - It has no filters that I needed. 

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

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

That worked. Thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.