Subtraction using the same column

[peterhui50]

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 Test_A_Measure to subtract Test_B_Measure, but because the units_Sold column is in one single column and I'm using keepfilters to get the totals for 2020 and 2021, Power BI doesn't seem to know where to put the totals.

I tried, CALCULATE( [Test_C], ALLEXCEPT(Table, Table'Company')) but it didn't work..

any advice would be great!

Thanks!!

[v-xiaotang · Accepted solution]

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.

[peterhui50]

Thanks for your review xiaotang, really appreciate it.

I found the measure now, it was in an archived thread. I can't take credit for it, but here it is.

Test_Earlier = 

CALCULATE (
    SUM ( 'Table'[Units_Sold] ),
    FILTER (
        ALLSELECTED('Table') ,
        'Table'[YEAR]
            = SELECTEDVALUE ( 'Table'[YEAR] ) - 1
            && 'Table'[Company] == SELECTEDVALUE ( 'Table'[Company] )
    )
)

The result is this..

[parry2k]

@peterhui50 send the pbix file, remove sensitive information before sharing, use one drive/google drive for sharing.

[parry2k]

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

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