Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Generate a summary table from different table.

I'm trying to generate a summary table B from different tables by date    Table B contains,  total orders, parameter B, total revenue.     What i'm using right now is multiple if conditon to ge...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous

    Are the tables related via "date" columns in "date" table?

    like:

    table1<->date table    table2<->date table   table3<->date table......

    Do you need to select dates then Total orders for each individual postion group shows accordingly?

     

    If so, try this measure:

    Total orders =
IF (
    MAX ( 'Summary_Revenue'[Position] ) = "Inbound Agents",
    CALCULATE (
        SUM ( 'In House COST PER CALL'[total_orders] ),
        ALLSELECTED ( Date[date] )
    ),
    IF (
        MAX ( 'Summary_Revenue'[Position] ) = "Senior CSR complaint",
        1,
        IF (
            MAX ( 'Summary_Revenue'[Position] ) = "At home agent",
            CALCULATE (
                SUM ( 'Home Agent COST PER CALL'[total orders] ),
                ALLSELECTED ( Date[date] )
            ),
            IF (
                MAX ( 'Summary_Revenue'[Position] ) = "D",
                CALCULATE (
                    SUM ( 'W Cost Per Call'[total orders] ),
                    ALLSELECTED ( Date[date] )
                ),
                IF (
                    MAX ( 'Summary_Revenue'[Position] ) = "W",
                    CALCULATE ( SUM ( O[total orders] ), ALLSELECTED ( Date[date] ) ),
                    BLANK ()
                )
            )
        )
    )
)

    Best Regards

    Maggie

     
v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

Hi Anonymous

Are the tables related via "date" columns in "date" table?

like:

table1<->date table    table2<->date table   table3<->date table......

Do you need to select dates then Total orders for each individual postion group shows accordingly?

 

If so, try this measure:

Total orders =
IF (
    MAX ( 'Summary_Revenue'[Position] ) = "Inbound Agents",
    CALCULATE (
        SUM ( 'In House COST PER CALL'[total_orders] ),
        ALLSELECTED ( Date[date] )
    ),
    IF (
        MAX ( 'Summary_Revenue'[Position] ) = "Senior CSR complaint",
        1,
        IF (
            MAX ( 'Summary_Revenue'[Position] ) = "At home agent",
            CALCULATE (
                SUM ( 'Home Agent COST PER CALL'[total orders] ),
                ALLSELECTED ( Date[date] )
            ),
            IF (
                MAX ( 'Summary_Revenue'[Position] ) = "D",
                CALCULATE (
                    SUM ( 'W Cost Per Call'[total orders] ),
                    ALLSELECTED ( Date[date] )
                ),
                IF (
                    MAX ( 'Summary_Revenue'[Position] ) = "W",
                    CALCULATE ( SUM ( O[total orders] ), ALLSELECTED ( Date[date] ) ),
                    BLANK ()
                )
            )
        )
    )
)

Best Regards

Maggie