Forum Discussion
Aggregated table with various column aggregations
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"
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] )
3 Replies
- Jihwan_Kim
Super User
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]
)
- Jihwan_Kim
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] )
