cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
marcos_osorio
Helper II
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.

 

TYPEQTYAMOUNTPROFIT
A262600165
B321252100
Diff-6134865

 

 

 

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

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" )
)

diff.png

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"
    )

1.png

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.

View solution in original post

2 REPLIES 2
v-yingjl
Community Support
Community Support

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" )
)

diff.png

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"
    )

1.png

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
Super User
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.






Did I answer your question? Mark my post as a solution.

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





Helpful resources

Announcements
2022 Release Wave 1 760x460.png

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.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors