Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
3 years ago
Solved

Subtract two columns to get progression

Good morning

I have a matrix that looks like this. Each value Loaded, Contacted, % Contact... are measures that are in the Values field in the matrix. I need to get a new column with the difference of the Current - Close column, i.e. Current Loaded - Closed Loaded etc.

To try to get something like that

Is there any way to get what I need?

Thank you all very much

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi Syndicate_Admin ,

    According to your description, here's my solution.

    1.Create a new table with a single column, create relationship with the fact table.

    2.Create new measures or directly modify the original measures.

    Contactados2 =
IF (
    MAX ( 'New'[New Column] ) = "Prog",
    CALCULATE (
        [Contactados],
        FILTER ( ALL ( 'Table' ), 'Table'[Column] = "Actual" )
    )
        - CALCULATE (
            [Contactados],
            FILTER ( ALL ( 'Table' ), 'Table'[Column] = "Cierre" )
        ),
    [Contactados]
)

    Aceptotas2 =
IF (
    MAX ( 'New'[New Column] ) = "Prog",
    CALCULATE (
        [Aceptotas],
        FILTER ( ALL ( 'Table' ), 'Table'[Column] = "Actual" )
    )
        - CALCULATE (
            [Aceptotas],
            FILTER ( ALL ( 'Table' ), 'Table'[Column] = "Cierre" )
        ),
    [Contactados]
)

    Get the correct result:

    I attach my sample below for your reference.

     

    Best regards,

    Community Support Team_yanjiang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi Syndicate_Admin ,

    According to your description, here's my solution.

    1.Create a new table with a single column, create relationship with the fact table.

    2.Create new measures or directly modify the original measures.

    Contactados2 =
IF (
    MAX ( 'New'[New Column] ) = "Prog",
    CALCULATE (
        [Contactados],
        FILTER ( ALL ( 'Table' ), 'Table'[Column] = "Actual" )
    )
        - CALCULATE (
            [Contactados],
            FILTER ( ALL ( 'Table' ), 'Table'[Column] = "Cierre" )
        ),
    [Contactados]
)

    Aceptotas2 =
IF (
    MAX ( 'New'[New Column] ) = "Prog",
    CALCULATE (
        [Aceptotas],
        FILTER ( ALL ( 'Table' ), 'Table'[Column] = "Actual" )
    )
        - CALCULATE (
            [Aceptotas],
            FILTER ( ALL ( 'Table' ), 'Table'[Column] = "Cierre" )
        ),
    [Contactados]
)

    Get the correct result:

    I attach my sample below for your reference.

     

    Best regards,

    Community Support Team_yanjiang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    3 years ago

    I was on vacation and hadn't seen the answer.

    Thank you you have solved the problem I had