Difference as total

[marcos_osorio]

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.

TYPE	QTY	AMOUNT	PROFIT
A	26	2600	165
B	32	1252	100
Diff	-6	1348	65

[v-yingjl · Accepted solution]

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.

[parry2k]

@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.⚡