Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
Hi all,
Sorry if there have been similar asks in the past but cant find the exact solution that I am looking for 🙂
I am trying to create a new aggregated table based on a specific value where all of the columns have a different function, then add additional columns to it. The purpose is to track a metric via the KPI visual.
This is an example of the current Order table:
Color | Order | Hours | Date |
Yellow | A | 5 | Jan-22 |
Yellow | B | 10 | Jan-22 |
Yellow | A | 22 | Feb-22 |
Blue | A | 11 | Jan-22 |
Blue | A | 77 | Jan-22 |
Blue | B | 5 | Jan-22 |
Blue | A | 8 | Feb-22 |
Blue | A | 15 | Mar-22 |
Expected Result: (only return aggregated columns when Order = A)
Color | Count of Order A | Avg Hours | Date | Period (New) | Period Goal (New) |
Yellow | 1 | 5 | Jan-22 | P1 | <=3 |
Yellow | 1 | 22 | Feb-22 | P2 | <=4 |
Blue | 2 | 44 | Jan-22 | P1 | <=3 |
Blue | 1 | 8 | Feb-22 | P2 | <=4 |
Blue | 1 | 15 | Mar-22 | P2 | <=4 |
logic:
step 1: calculate Period based on defined months (jan = P1, feb/mar = p2)
step 2: calculate Period goal based on Period column (p1 goal avg hrs<=3, p2 goal avg hrs<=4)
Step 3:if Order = A, then return group by "Color", count of "Order", average "Hours", "Period Goal"
Solved! Go to Solution.
Hi,
I am not sure if I understood your question correctly, but could you please try the below?
New AGG Table =
SUMMARIZE (
FILTER (
ADDCOLUMNS (
SUMMARIZE ( 'Order', 'Order'[Order], 'Order'[Color], 'Order'[Date] ),
"@CountOrders", CALCULATE ( COUNTROWS ( 'Order' ) ),
"@AvgHrs", CALCULATE ( AVERAGE ( 'Order'[Hours] ) ),
"@Date", FORMAT ( 'Order'[Date], "mmm yy" ),
"@Period",
IF (
'Order'[Date] >= DATE ( 2022, 2, 1 )
&& 'Order'[Date] <= DATE ( 2022, 3, 31 ),
"P1",
"P2"
),
"@PeriodGoal",
IF (
'Order'[Date] >= DATE ( 2022, 2, 1 )
&& 'Order'[Date] <= DATE ( 2022, 3, 31 ),
"<=3",
"<=4"
)
),
'Order'[Order] = "A"
),
'Order'[Color],
[@CountOrders],
[@AvgHrs],
[@Date],
[@Period],
[@PeriodGoal]
)
Hi,
Please check the below DAX formula for creating a new table, and the attached pbix file.
New Agg Table =
SUMMARIZE (
FILTER (
ADDCOLUMNS (
SUMMARIZE ( 'Order', 'Order'[Order], 'Order'[Color], 'Order'[Date] ),
"@CountOrders", CALCULATE ( COUNTROWS ( 'Order' ) ),
"@AvgHrs", CALCULATE ( AVERAGE ( 'Order'[Hours] ) ),
"@Date", FORMAT ( 'Order'[Date], "mmm yy" ),
"@Period",
IF (
MONTH ( 'Order'[Date] ) = 1,
"P1",
IF ( MONTH ( 'Order'[Date] ) = 2 || MONTH ( 'Order'[Date] ) = 3, "P2" )
),
"@PeriodGoal",
IF (
MONTH ( 'Order'[Date] ) = 1,
"<=3",
IF ( MONTH ( 'Order'[Date] ) = 2 || MONTH ( 'Order'[Date] ) = 3, "<=4" )
)
),
'Order'[Order] = "A"
),
'Order'[Color],
[@CountOrders],
[@AvgHrs],
[@Date],
[@Period],
[@PeriodGoal]
)
Hi @Jihwan_Kim thanks for the quick reply! 🙂 I would need to create date ranges for @Period so I have changed "month" to "datesbetween" but I am getting an "Argument '3' in IF statment required" error. Below is what I am looking for using P2 as an example. Thoughts?
New Agg Table =
SUMMARIZE (
FILTER (
ADDCOLUMNS (
SUMMARIZE ( 'Order', 'Order'[Order], 'Order'[Color], 'Order'[Date] ),
"@CountOrders", CALCULATE ( COUNTROWS ( 'Order' ) ),
"@AvgHrs", CALCULATE ( AVERAGE ( 'Order'[Hours] ) ),
"@Date", FORMAT ( 'Order'[Date], "mmm yy" ),
"@Period",
IF (
datesbetween('Order'[Date],DATE(2022,02,01),DATE(2022,03,31))= 1,
"P2",
),
"@PeriodGoal",
IF ((
datesbetween('Order'[Date],DATE(2022,02,01),DATE(2022,03,31))= 1,
"<=3",
)
),
'Order'[Order] = "A"
),
'Order'[Color],
[@CountOrders],
[@AvgHrs],
[@Date],
[@Period],
[@PeriodGoal]
)
Hi,
I am not sure if I understood your question correctly, but could you please try the below?
New AGG Table =
SUMMARIZE (
FILTER (
ADDCOLUMNS (
SUMMARIZE ( 'Order', 'Order'[Order], 'Order'[Color], 'Order'[Date] ),
"@CountOrders", CALCULATE ( COUNTROWS ( 'Order' ) ),
"@AvgHrs", CALCULATE ( AVERAGE ( 'Order'[Hours] ) ),
"@Date", FORMAT ( 'Order'[Date], "mmm yy" ),
"@Period",
IF (
'Order'[Date] >= DATE ( 2022, 2, 1 )
&& 'Order'[Date] <= DATE ( 2022, 3, 31 ),
"P1",
"P2"
),
"@PeriodGoal",
IF (
'Order'[Date] >= DATE ( 2022, 2, 1 )
&& 'Order'[Date] <= DATE ( 2022, 3, 31 ),
"<=3",
"<=4"
)
),
'Order'[Order] = "A"
),
'Order'[Color],
[@CountOrders],
[@AvgHrs],
[@Date],
[@Period],
[@PeriodGoal]
)
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.