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 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!!

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.

5 Replies

  • 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

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

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

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    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.

    • peterhui50's avatar
      peterhui50
      Helper III
      4 years ago

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