Forum Discussion

peterhui50's avatar
peterhui50
Helper III
4 years ago
Solved

Subtraction using the same column

Hi,   I have a table here, in Power BI, I am using a table visual, Test_A and Test_B are measures and Test_C is a subtraction both.   but basically I want it to do this..     I want the...
General Comment
Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi peterhui50 

    try this

    Test_C = 
VAR _date1 =
    DATE ( 2020, 3, 31 )
VAR _date2 =
    DATE ( 2021, 3, 31 )
VAR _value1 =
    CALCULATE (
        MIN ( 'Table'[Units_Sold] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Company] ),
            'Table'[Date] = _date1
        )
    )
RETURN
    IF (
        MIN ( 'Table'[Date] ) = _date1,
        MIN ( 'Table'[Units_Sold] ),
        IF (
            MIN ( 'Table'[Date] ) = _date2,
            MIN ( 'Table'[Units_Sold] ) - _value1,
            BLANK ()
        )
    )

    result

     

     

    Best Regards,

    Community Support Team _Tang

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

parry2k's avatar
parry2k
Super User
4 years ago

peterhui50 if you put compare, and measures in a table visual, it should work.

 

✨ Follow us on LinkedIn

 

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) 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.⚡

peterhui50's avatar
peterhui50
Helper III
4 years ago

Doesn't seem to work, I think it is because the row context for Test_B_Measure is mssing ?  Test_A_Measure is subtracting nothing.... this is why company_A in Test_C Measure returns -5  instead of (4 - 5) which is -1

 

Any help would be great