cancel
Showing results for
Did you mean:
Helper II

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.

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

1 ACCEPTED SOLUTION
Community Support

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 2
Community Support

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.

Super User

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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Announcements

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.