Aggregated table with various column aggregations

[Anon46]

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"

[Jihwan_Kim]

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

[Anon46]

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 · Accepted 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]
)