Generate a summary table from different table.

[Anonymous]

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 get data from different tables total Orders

Total orders =

IF('Summary_Revenue'[Position] = "Inbound Agents", SUM('In House COST PER CALL'[total_orders]),

IF('Summary_Revenue'[Position] = "Senior CSR complaint",1,

IF('Summary_Revenue'[Position] = "At home agent",SUM('Home Agent COST PER CALL'[total orders]),

IF('Summary_Revenue'[Position] = "D",SUM('W Cost Per Call'[total orders]),

IF('Summary_Revenue'[Position] = "W",SUM(O[total orders]),

BLANK()

)))))

But what i'm looking for is a day summary for each individual postion group by its date.

How can i reach such result?

[v-juanli-msft · Accepted solution]

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