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 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?
 
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

     

1 Reply

  • 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