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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Creating New Table Aggregation From Another

Hello -

 

New to the forum and new to Power BI -  I have worked with SQL extensively in the past and am trying to relate to what i am looking to solve. I have the below table located right now - within the Phase Column, i have 4 values; budget, purchase order, requisitions, and GL. I'd like to create an identical table (keeping same structure), however, in the Phase column i'd like to rename to "act/fcst" and sum where phase = 'purchase order', 'requisiton'. This data would be grouped by all other columns listed in the table. The purchase order and requisition are on different rows and do not exist as a separate column so i cannot add up. Any advice on the DAX coding? i have looked all over and cannot seem to figure the syntax out. Thanks.

 

Capture.PNG

 
 
1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

So, that should be something along the lines of:

 

Table1 =
SELECTCOLUMNS(
  GROUPBY(
    FILTER(
      'Table',
      'Table'[PHASE] = 'purchase order' || 'Table'[PHASE]="requisition"
    ),
    [DATE],
    [SITE],
    [REGION],
    [STATUS],
    [DEPT_DESC],
    [P01_DESC],
    [UseMonth]
    [PHASE]
    [DEPTRANK],
    [P01 Use],
    "AMOUNT",SUMX(CURRENTGROUP(),[AMOUNT])
  ),
"act/fcst",[PHASE],"DATE",[DATE],"SITE",[SITE],"STATUS",[STATUS],"DEPT_DESC",[DEPT_DESC],"P01_DESC",[P01_DESC],"AMOUNT",[AMOUNT],"UseMonth",[UseMonth],"DEPTRANK",[DEPTRANK],"P01 Use",[P01 Use]
)
    

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

1 REPLY 1
Greg_Deckler
Super User
Super User

So, that should be something along the lines of:

 

Table1 =
SELECTCOLUMNS(
  GROUPBY(
    FILTER(
      'Table',
      'Table'[PHASE] = 'purchase order' || 'Table'[PHASE]="requisition"
    ),
    [DATE],
    [SITE],
    [REGION],
    [STATUS],
    [DEPT_DESC],
    [P01_DESC],
    [UseMonth]
    [PHASE]
    [DEPTRANK],
    [P01 Use],
    "AMOUNT",SUMX(CURRENTGROUP(),[AMOUNT])
  ),
"act/fcst",[PHASE],"DATE",[DATE],"SITE",[SITE],"STATUS",[STATUS],"DEPT_DESC",[DEPT_DESC],"P01_DESC",[P01_DESC],"AMOUNT",[AMOUNT],"UseMonth",[UseMonth],"DEPTRANK",[DEPTRANK],"P01 Use",[P01 Use]
)
    

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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