Forum Discussion

Anon46's avatar
Anon46
Frequent Visitor
3 years ago
Solved

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 wher...
Need Help
Tips and Tricks
Tutorial Requests
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    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]
)
Anon46's avatar
Anon46
Frequent Visitor
3 years ago

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's avatar
Jihwan_Kim
Icon for Super User rankSuper User
3 years ago

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