Forum Discussion

marcos_osorio's avatar
marcos_osorio
Helper II
5 years ago
Solved

Difference as total

Hi, I have a table with data from two different reports., each type has the same attributes. 

Is there a way to make the BI display the difference in the total? I mean to avoid creating one measure for each attribute for each type and calculate the difference.

 

Thank you very much.

 

TYPEQTYAMOUNTPROFIT
A262600165
B321252100
Diff-6134865

 

 

 

Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi marcos_osorio ,

    In power bi, Dax is calculated based on context and columns, if you want to get the same result in the table, you need to create three simliar measures:

    QTY_ = 
IF (
    HASONEVALUE ( 'Table A'[QTY] ),
    SUM ( 'Table A'[QTY] ),
    CALCULATE ( SUM ( 'Table A'[QTY] ), 'Table A'[TYPE] = "A" )
        - CALCULATE ( SUM ( 'Table A'[QTY] ), 'Table A'[TYPE] = "B" )
)

Amount_ = 
IF (
    HASONEVALUE ( 'Table A'[AMOUNT] ),
    SUM ( 'Table A'[AMOUNT] ),
    CALCULATE ( SUM ( 'Table A'[AMOUNT] ), 'Table A'[TYPE] = "A" )
        - CALCULATE ( SUM ( 'Table A'[AMOUNT] ), 'Table A'[TYPE] = "B" )
)

PROFIT_ = 
IF (
    HASONEVALUE ( 'Table A'[PROFIT] ),
    SUM ( 'Table A'[PROFIT] ),
    CALCULATE ( SUM ( 'Table A'[PROFIT] ), 'Table A'[TYPE] = "A" )
        - CALCULATE ( SUM ( 'Table A'[PROFIT] ), 'Table A'[TYPE] = "B" )
)

    If you only want to create one measure, you need to unpivot your table and use a matrix to show that:

    Diff = 
CALCULATE (
    SUM ( 'Table A - Unpivot'[Value] ),
    'Table A - Unpivot'[TYPE] = "A"
)
    - CALCULATE (
        SUM ( 'Table A - Unpivot'[Value] ),
        'Table A - Unpivot'[TYPE] = "B"
    )

    Attached a sample file in the below, hopes it could help.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    marcos_osorio yes if you unpivot your data then you don't need to write measure for each column, only 3 measures will be required in that case.

     

    Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

  • v-yingjl's avatar
    v-yingjl
    Community Support
    5 years ago

    Hi marcos_osorio ,

    In power bi, Dax is calculated based on context and columns, if you want to get the same result in the table, you need to create three simliar measures:

    QTY_ = 
IF (
    HASONEVALUE ( 'Table A'[QTY] ),
    SUM ( 'Table A'[QTY] ),
    CALCULATE ( SUM ( 'Table A'[QTY] ), 'Table A'[TYPE] = "A" )
        - CALCULATE ( SUM ( 'Table A'[QTY] ), 'Table A'[TYPE] = "B" )
)

Amount_ = 
IF (
    HASONEVALUE ( 'Table A'[AMOUNT] ),
    SUM ( 'Table A'[AMOUNT] ),
    CALCULATE ( SUM ( 'Table A'[AMOUNT] ), 'Table A'[TYPE] = "A" )
        - CALCULATE ( SUM ( 'Table A'[AMOUNT] ), 'Table A'[TYPE] = "B" )
)

PROFIT_ = 
IF (
    HASONEVALUE ( 'Table A'[PROFIT] ),
    SUM ( 'Table A'[PROFIT] ),
    CALCULATE ( SUM ( 'Table A'[PROFIT] ), 'Table A'[TYPE] = "A" )
        - CALCULATE ( SUM ( 'Table A'[PROFIT] ), 'Table A'[TYPE] = "B" )
)

    If you only want to create one measure, you need to unpivot your table and use a matrix to show that:

    Diff = 
CALCULATE (
    SUM ( 'Table A - Unpivot'[Value] ),
    'Table A - Unpivot'[TYPE] = "A"
)
    - CALCULATE (
        SUM ( 'Table A - Unpivot'[Value] ),
        'Table A - Unpivot'[TYPE] = "B"
    )

    Attached a sample file in the below, hopes it could help.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.